To 🔥 celebrate the holiday season, Also check out our NEWLY launched Annual plan (Up to 50% off). Use coupon code THANKSGIVING15 and enjoy additional 15% off all paid plans. Coupon expires at 2020/11/30 .
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: 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               | USER-DEFINED
 last_update          | timestamp with time zone

  

Table 2: 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
 last_update  | timestamp with time zone

  

Table 3: inventory

Each row is unique; Inventoy_id is the primary key of the table

   col_name   | col_type
--------------+--------------------------
 inventory_id | integer
 film_id      | smallint
 store_id     | smallint
 last_update  | timestamp with time zone

  

Table 4: 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 5: film_category

A film can only belong to one category

  col_name   | col_type
-------------+--------------------------
 film_id     | smallint
 category_id | smallint
 last_update | timestamp with time zone

  

Table 6: category

Movie categories.

  col_name   | col_type
-------------+--------------------------
 category_id | integer
 name        | text
 last_update | timestamp with time zone

  

Sample results

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

Subscribe for solution