Müşteri gruplarının favori oyuncularını belirleme

SQL ile Veri Odaklı Karar Verme

Irene Ortner

Data Scientist at Applied Statistics

Tek sorguda SQL ifadelerini birleştirme

LEFT JOIN
WHERE
GROUP BY
HAVING
ORDER BY

Kiralama kayıtlarından müşteri ve oyuncu bilgisine

Sorumuz: Belirli bir müşteri grubu için favori oyuncu kim?

renting tablosunu şu tablolarla birleştirin:

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;

Erkek müşteriler

Erkek müşterilerin izlediği filmlerde en sık oynayan oyuncular.

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;

Favori oyuncu kim?

En çok izlenen oyuncu.
İzlenince en yüksek ortalama puan.

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 ve ORDER BY ekleme

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;

HAVING ve ORDER BY ekleme

| 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       |

Hadi pratik yapalım!

SQL ile Veri Odaklı Karar Verme