Final example

Data-Driven Decision Making in SQL

Tim Verdonck

Professor Statistics and Data Science

Business Case

MovieNow considers to invest money in new movies.
It is more expensive for MovieNow to make movies available which were recently produced than older ones.
First step of data analysis:
- Do customers give better ratings to movies which were recently produced than to older ones?
- Is there a difference across countries?

1. Join data

Information needed:
- renting records of movie rentals with ratings
- customers information about country of the customer
- movies year of release of the movie
```
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. Select relevant records

Use only records of movies with at least 4 ratings
Use only records of movie rentals since 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. Aggregation

Type of aggregation:

Count the number of movie rentals
Count the number of different movies
Calculate the average rating

Levels of aggregation:

Total aggregation
For movies by year of release
For movies by year of release separately for the country of the customers

3. Aggregation

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;

Resulting table

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

Let's practice!

Data-Driven Decision Making in SQL