Section 1 | Disney Movies

Dataset

All Disney movies with their release dates, genre, rating, total gross and inflation adjusted gross(2016).
Disney has produced many movies, this data has all Disney movies till 2016.

Source: https://www.kaggle.com/datasets/prateekmaj21/disney-movies/data

Run the SQL statements here to populate the data
DDL

Some interesting questions we can ask

What are the number of movies released by year

select extract(year from release_date) as release_year, 
count(*) as num_movies
from disney_movies group by release_year order by num_movies desc;

Similarly, number of movies by genre and rating

select mpaa_rating, count(*) from disney_movies group by mpaa_rating;

select genre, count(*) from disney_movies group by genre;

Top grossing genre and top movies in the genre

This is bit more involved, what if we want to find the top grossing genre and top 3 movies in each of those genres.

WITH ranked_genres AS (
    SELECT
        genre,
        total_gross as bo_collection,
        movie_title,
        ROW_NUMBER() OVER (PARTITION BY genre ORDER BY total_gross DESC) AS rn
    FROM
        disney_movies
)
SELECT
    *
FROM
    ranked_genres
WHERE
    rn <= 3;

WITH ranked_genres AS (
    SELECT
        genre,
        movie_title,
          total_gross,
        --SUM(total_gross) OVER (PARTITION BY genre ORDER BY total_gross desc) AS running_sum,
          ROW_NUMBER() OVER (PARTITION BY genre ORDER BY total_gross desc) AS rn
    FROM
        disney_movies
),
per_genre as (
  select genre, sum(total_gross) as total_gross_for_genre from ranked_genres
  group by genre
)
SELECT
    rg.genre, rg.movie_title, rg.total_gross as total_gross,
    pg.total_gross_for_genre
FROM
    ranked_genres rg
    inner join per_genre pg on rg.genre=pg.genre 
WHERE
    rn <= 3 order by total_gross_for_genre desc;