#60 movie rentals by category

Hi,

Would love some insight into why my solution is incorrect and producing the wrong avg percentage?

This is my solution:

with tablee as(
select i.film_id, c.name as category_name, count(*) as rentals
from rental r 
inner join inventory i on r.inventory_id=i.inventory_id
inner join film_category fc on i.film_id=fc.film_id
inner join category c on fc.category_id=c.category_id
where i.film_id<=10
group by 1,2)

select film_id, category_name, rentals, 
avg(rentals) over(partition by category_name) as avg_rentals_category
from tablee
group by 1,2

And this was the correct solution:

WITH movie_rental AS (
    SELECT
        I.film_id,
        COUNT(*) rentals
    FROM rental R
    INNER JOIN inventory I
    ON I.inventory_id = R.inventory_id
    GROUP BY I.film_id
)
SELECT 
    film_id, 
    category_name, 
    rentals, 
    avg_rentals_category 
FROM (
    SELECT
        MR.film_id,
        C.name category_name,
        rentals,
        AVG(rentals) OVER(PARTITION BY C.name) avg_rentals_category
    FROM movie_rental MR
    INNER JOIN film_category FC
      ON FC.film_id = MR.film_id
    INNER JOIN category C
      ON C.category_id = FC.category_id
) X
WHERE film_id <= 10
;

I guess I'm mainly wondering why does doing it all in one not work? And could someone help me understand the logic behind why it doesn't work. Any help appreciated thanks!