Laatste voorbeeld

Data-driven beslissingen nemen met SQL

Tim Verdonck

Professor Statistics and Data Science

Businesscase

MovieNow overweegt te investeren in nieuwe films.
Recent uitgebrachte films aanbieden is duurder dan oudere.
Eerste stap in de analyse:
- Geven klanten hogere ratings aan recentere films dan aan oudere?
- Is er verschil per land?

1. Data joinen

Nodige info:

renting records van verhuur met ratings
customers land van de klant

movies jaar van uitgave

SELECT *
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id;

2. Relevante records selecteren

Gebruik alleen films met ≥ 4 ratings
Gebruik alleen verhuur sinds 2018-04-01

SELECT *
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
    SELECT movie_id
    FROM renting
    GROUP BY movie_id
    HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01';

3. Aggregatie

Type aggregatie:

Aantal verhuur tellen
Aantal verschillende films tellen
Gemiddelde rating berekenen

Aggregatieniveaus:

Totaalaggregatie
Per jaar van uitgave
Per jaar van uitgave, uitgesplitst naar land

3. Aggregatie

SELECT c.country,
       m.year_of_release,
       COUNT(*) AS n_rentals,
       COUNT(DISTINCT r.movie_id) AS n_movies,
       AVG(rating) AS avg_rating
FROM renting AS r
LEFT JOIN customers AS c
ON c.customer_id = r.customer_id
LEFT JOIN movies AS m
ON m.movie_id = r.movie_id
WHERE r.movie_id IN (
    SELECT movie_id
    FROM renting
    GROUP BY movie_id
    HAVING COUNT(rating) >= 4)
AND r.date_renting >= '2018-04-01'
GROUP BY ROLLUP (m.year_of_release, c.country)
ORDER BY c.country, m.year_of_release;

Resultaattabel

| year_of_release | country | n_rentals | n_movies | avg_rating         |
|-----------------|---------|-----------|----------|--------------------|
| 2009            | null    | 10        | 1        | 8.7500000000000000 | 
| 2010            | null    | 41        | 5        | 7.9629629629629630 | 
| 2011            | null    | 14        | 2        | 8.2222222222222222 | 
| 2012            | null    | 28        | 5        | 8.1111111111111111 | 
| 2013            | null    | 10        | 2        | 7.6000000000000000 | 
| 2014            | null    | 5         | 1        | 8.0000000000000000 | 
| null            | null    | 333       | 50       | 7.9024390243902439 |

Laten we oefenen!

Data-driven beslissingen nemen met SQL