Posted by Amarendra, April 23, 2022, 1:27 p.m.
Can someone help me understand why the below code is not converting the buckets into a percentile?
select
film_id,
revenue,
NTILE(100) over(order by revenue) as percentile
from (
select
f.film_id,
SUM(p.amount) as revenue
from film f
join inventory i ON i.film_id = f.film_id
join rental r ON r.inventory_id = i.inventory_id
join payment p ON p.rental_id = r.rental_id
group by 1
)x
where film_id IN (1,10,11,20,21,30)
Hi Amerendra,
This is a great question, it's related to the order of how the database engine executes your code.
The reason it didn't pass the test is that NTILE will run after the WHERE condition, so the NTILE was only applied on 6 rows.
To compute percentages, it has to be applied to all rows. Moving the WHERE condition after NTILE is ran will solve the problem:
SELECT * FROM (
select
film_id,
revenue,
NTILE(100) over(order by revenue) as percentile
from (
select
f.film_id,
SUM(p.amount) as revenue
from film f
join inventory i ON i.film_id = f.film_id
join rental r ON r.inventory_id = i.inventory_id
join payment p ON p.rental_id = r.rental_id
group by 1
)x
) Y
where film_id IN (1,10,11,20,21,30)
