SQLPad Forum question: 60. Movie rentals and average rentals in the …

60. Movie rentals and average rentals in the same category

with cte as
(SELECT i.film_id,c.name,count(*) as rentals from rental r join inventory i on r.inventory_id=i.inventory_id
join film_category fc on i.film_id=fc.film_id
join category c on fc.category_id=c.category_id
group by i.film_id)

select film_id,name,rentals,avg(rentals) over(partition by name) as avg_rentals from cte
where film_id<=10
order by name;

Why does my answer differ from the actual one? I need to know where I am going wrong in my approach.

Answers

Comment

Hi Abbas,

This is an important question, and your code didn't generate the correct one because the avg function applied on the filtered results, which means it was the average of the film_id <= 10.

Again, this boils down to how the engine executes your code:

Code 1:

select film_id,name,rentals,avg(rentals) over(partition by name) as avg_rentals from cte
where film_id<=10
order by name;

The avg function is based on film_id <= 10, only the first 10 films, but we want to get the average of based on all films.

Modifed version should work:

Code 2: (correct one)

SELECT * FROM (
    select film_id,name,rentals,avg(rentals) over(partition by name) as avg_rentals from cte
) X
WHERE X.film_id<=10

Leon (949)

Oct. 4, 2023, 8:53 a.m.