Grouping movies

Data-Driven Decision Making in SQL

Bart Baesens

Professor Data Science and Analytics

GROUP BY Applications

  • Preferences of customers by country or gender.

  • The popularity of movies by genre or year of release.

  • The average price of movies by genre.

Data-Driven Decision Making in SQL

Table: movies_selected

Data-Driven Decision Making in SQL

GROUP BY

SELECT genre
FROM movies_selected
GROUP BY genre;
| genre       |
|-------------|
| Drama       |
| Fantasy     |
| Sci-Fiction |
| Animation   |
| Romance     |
Data-Driven Decision Making in SQL

Average renting price

SELECT genre, 
       AVG(renting_price) AS avg_price
FROM movies_selected
GROUP BY genre;
| genre       | avg_price   |
|-------------|-------------|
| Drama       | 2.865       |
| Fantasy     | 2.69        |
| Sci-Fiction | 2.87        |
| Animation   | 2.923333333 |
| Romance     | 2.99        |
Data-Driven Decision Making in SQL

movies_selected table

Data-Driven Decision Making in SQL

movies_selected table split

Data-Driven Decision Making in SQL

Average rental price and number of movies

SELECT genre, 
       AVG(renting_price) AS avg_price, 
       COUNT(*) AS number_movies
FROM movies_selected
GROUP BY genre
| genre       | avg_price     | number_movies |
|-------------|---------------|---------------|
| Drama       | 2.865         | 4             |
| Fantasy     | 2.69          | 3             |
| Sci-Fiction | 2.87          | 2             |
| Animation   | 2.923333333   | 3             |
| Romance     | 2.99          | 1             |
Data-Driven Decision Making in SQL

HAVING

SELECT genre, 
       AVG(renting_price) avg_price, 
       COUNT(*) number_movies
FROM movies
GROUP BY genre
HAVING COUNT(*) > 2;
| genre     | avg_price     | number_movies |
|-----------|---------------|---------------|
| Drama     | 2.865         | 4             |
| Fantasy   | 2.69          | 3             |
| Animation | 2.923333333   | 3             |
Data-Driven Decision Making in SQL

Let's practice!

Data-Driven Decision Making in SQL

Preparing Video For Download...