Forum

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

Answers

Hi Ricky,

Thanks for the question, We went ahead and updated the sample results.

The purpose of showing the sample results are just to show the expected format, and not the ground truth.

Thanks and happy SELECTing!

Leon

SQLPad user avatar

Leon (949)

Feb. 5, 2023, 10:06 a.m.