Identifikasi aktor favorit per kelompok pelanggan

Pengambilan Keputusan Berbasis Data dengan SQL

Irene Ortner

Data Scientist at Applied Statistics

Menggabungkan pernyataan SQL dalam satu kueri

LEFT JOIN
WHERE
GROUP BY
HAVING
ORDER BY

Dari catatan renting ke info pelanggan dan aktor

Pertanyaan kita: Siapa aktor favorit untuk kelompok pelanggan tertentu?

Gabungkan tabel renting dengan

customers
actsin
actors

SELECT *
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id;

Pelanggan laki-laki

Aktor yang paling sering tampil di film yang ditonton pelanggan laki-laki.

SELECT a.name, 
       COUNT(*)
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id

WHERE c.gender = 'male'
GROUP BY a.name;

Siapa aktor favoritnya?

Aktor yang paling sering ditonton.
Rata-rata rating terbaik saat ditonton.

SELECT a.name, 
       COUNT(*) AS number_views, 
       AVG(r.rating) AS avg_rating
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id

WHERE c.gender = 'male'
GROUP BY a.name;

Tambahkan HAVING dan ORDER BY

SELECT a.name, 
       COUNT(*) AS number_views, 
       AVG(r.rating) AS avg_rating
FROM renting as r
LEFT JOIN customers AS c
ON r.customer_id = c.customer_id
LEFT JOIN actsin as ai
ON r.movie_id = ai.movie_id
LEFT JOIN actors as a
ON ai.actor_id = a.actor_id

WHERE c.gender = 'male'
GROUP BY a.name
HAVING AVG(r.rating) IS NOT NULL
ORDER BY avg_rating DESC, number_views DESC;

Tambahkan HAVING dan ORDER BY

| name               | number_views | avg_rating |
|--------------------|--------------|------------|
| Ray Romano         | 3            | 10.00      |
| Sean Bean          | 2            | 10.00      |
| Leonardo DiCaprio  | 3            | 9.33       |
| Christoph Waltz    | 3            | 9.33       |

Ayo berlatih!

Pengambilan Keputusan Berbasis Data dengan SQL