Posted by Ricky, Feb. 1, 2023, 11:28 a.m.
Q58 - Sample results different from expected results
As the title suggests, it seems the sample results are different from the expected results. The samples results uses the total revenue from all movies, while the expected results uses the total revenue for all films <=10.
Here is my query:
with a as (
select
sum(p.amount) as total_revenue
from
payment as p
left join rental as r on p.rental_id=r.rental_id
left join inventory as i on r.inventory_id = i.inventory_id
)
, b as (
select
i.film_id
, sum(p.amount) as cohort_revenue
from
payment as p
left join rental as r on p.rental_id=r.rental_id
left join inventory as i on r.inventory_id = i.inventory_id
where i.film_id <= 10
group by i.film_id
)
select
b.film_id
,b.cohort_revenue/a.total_revenue*100 as revenue_percentage
from
b, a