Table 1: category
Movie categories.
col_name | col_type -------------+-------------------------- category_id | integer name | text
Question 59
Percentage of revenue per movie by category
Instruction
- Write a query to return the percentage of revenue for each of the following films:
film_id <= 10by 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 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
Solution
postgresWITH 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.
Copied
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Copied
Expected results
Submit a query to compare against expected output.
Postgres
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
10:00
Your results
Run your query to preview results here.