49. Top 3 money making movie categories

medium

Instruction
  • Write a query to return the name of the 3 movie categories that generated the most rental revenue
  • And rental revenue from each of the category.
  • The order of your results doesn't matter.
  • If there are ties, return just one of them.

Table 1: category

Movie categories.

  col_name   | col_type
-------------+--------------------------
 category_id | integer
 name        | text

Table 2: film

       col_name       |  col_type
----------------------+--------------------------
 film_id              | integer
 title                | text
 description          | text
 release_year         | integer
 language_id          | smallint
 original_language_id | smallint
 rental_duration      | smallint
 rental_rate          | numeric
 length               | smallint
 replacement_cost     | numeric
 rating               | text

Table 3: film_category

A film can only belong to one category

  col_name   | col_type
-------------+--------------------------
 film_id     | smallint
 category_id | smallint

Table 4: inventory

Each row is unique, inventoy_id is the primary key of this table.

   col_name   | col_type
--------------+--------------------------
 inventory_id | integer
 film_id      | smallint
 store_id     | smallint

Table 5: payment

Movie rental payment transactions table

   col_name   | col_type
--------------+--------------------------
 payment_id   | integer
 customer_id  | smallint
 staff_id     | smallint
 rental_id    | integer
 amount       | numeric
 payment_ts   | timestamp with time zone

Table 6: rental

   col_name   | col_type
--------------+--------------------------
 rental_id    | integer
 rental_ts    | timestamp with time zone
 inventory_id | integer
 customer_id  | smallint
 return_ts    | timestamp with time zone
 staff_id     | smallint

Sample results

    name     |   revenue
-------------+---------
 Sports      | 123
 Sci-Fi      | 456
 Animation   | 789

Expected results

Solution postgres

SELECT C.name, SUM(P.amount)
FROM 
    payment P
INNER JOIN rental R 
ON R.rental_id = P.rental_id
INNER JOIN inventory I
ON I.inventory_id = R.inventory_id
INNER JOIN film F
ON F.film_id = I.film_id
INNER JOIN film_category FC
ON FC.film_id = F.film_id
INNER JOIN category C
ON C.category_id = FC.category_id
GROUP BY C.name 
ORDER BY SUM(P.amount) DESC
LIMIT 3
;
    

Explanation

This query retrieves the names of the top 3 movie categories that have earned the most money through rentals.

The query starts with the payment table and joins it with the rental table based on the rental ID. Then it joins the inventory table using the inventory ID, followed by the film table using the film ID.

Next, it joins the film_category table using the film ID, and finally the category table using the category ID.

The query groups the results by category name, calculates the total amount earned from rentals in each category using the SUM() function, and orders the results in descending order by the total amount earned.

Finally, it limits the query to only return the top 3 results.



More INNER JOIN questions

ID Title Level FTPR
179 Members who ever moved from Microsoft to Google linkedin medium
31%
162 Number of orders per brand walmart easy
41%
150 Countries with above average customers social easy
15%
99 Page recommendation social medium
13%
91 same day friend request acceptance rate spotify easy
13%
50 Top 5 cities for movie rentals easy
22%
48 Movie and TV actors easy
38%
40 Top 2 most rented movie in June 2020 medium
14%
39 Most productive actor with inner join easy
22%
38 Most popular movie category (name and id) medium
19%
37 Most popular movie category easy
15%
36 Actors from film 'AFRICAN EGG' easy
32%