- 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 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.