select
    t.product_name,
    t.total_sales,
    dense_rank() over(partition by t.category order by t.total_sales desc) as category_rank
from
    (select
        p.name as product_name,
        p.category,
        p.product_id,
        sum(o.quantity) as total_sales
    from 
        products p
    left join
        orders o on o.product_id = p.product_id
    group by 
        1,2,3
    having total_sales is not null) t
order by 
    t.category,t.total_sales desc