59. Percentage of revenue per movie by category

medium

Instruction

  • Write a query to return the percentage of revenue for each of the following films: film_id <= 10 by its category.
  • Formula: revenue (film_id x) * 100.0/ revenue of all movies in the same category.
  • The order of your results doesn't matter.
  • Return 3 columns: film_id, category name, and percentage.

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

 film_id | category_name | revenue_percent_category
---------+---------------+--------------------------
       1 | Documentary   |   0.87183937479845975834
       2 | Horror        |       1.4218786097664498
       3 | Documentary   |   0.89815815929740700696
       4 | Horror        |       2.4652522202582108
       5 | Family        |       1.2276180943524362

Expected results

Solution postgres

WITH movie_revenue AS (
	SELECT
	    I.film_id, SUM(P.amount) revenue
	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	
	GROUP BY I.film_id
),rev_percentage AS (
SELECT 
    MR.film_id, 
    C.name category_name,
    revenue * 100.0 / SUM(revenue) OVER(PARTITION BY C.name) revenue_percent_category
FROM movie_revenue MR
INNER JOIN film_category FC
  ON FC.film_id = MR.film_id
INNER JOIN category C
  ON C.category_id = FC.category_id
)
SELECT * FROM rev_percentage
WHERE film_id <= 10
ORDER BY film_id
;
    

Explanation

This query is trying to analyze the percentage of movie revenue for each category of films.

The first part of the query creates a temporary table called `movie_revenue` that sums up the total revenue for each movie by joining the `payment`, `rental`, and `inventory` tables.

The second part of the query creates another temporary table called `rev_percentage` that calculates the percentage of revenue for each movie within its corresponding category. It does this by joining the `movie_revenue` table with the `film_category` and `category` tables, and then using the `SUM()` function with the `OVER()` clause to calculate the total revenue for each category.

Finally, the query selects all the columns from the `rev_percentage` table where the film_id is less than or equal to 10 and sorts the results by film_id.

Overall, this query helps a data analyst understand how movie revenue is distributed among different categories of films.



More AVG, MIN/MAX, SUM questions

ID Title Level FTPR
189 Campaign activation date twitter medium
0%
182 Top 5 content by number of watchers netflix medium
4%
141 Cumulative sales of restaurant 100011 doordash hard
16%
117 Rolling 7 days total answers amazon easy
35%
115 Rolling average revenue amazon hard
26%
113 Daily cumulative spend amazon easy
9%
108 Free premium membership amazon hard
17%
71 Cumulative rentals easy
18%
70 Cumulative spend easy
14%
61 Customer spend vs average spend in the same store hard
12%
60 Movie rentals and average rentals in the same category hard
9%
58 Percentage of revenue per movie hard
8%