select p.name as product_name,
    SUM(o.quantity) AS total_sales,
    dense_rank() over(partition by p.category 
        order by sum(o.quantity) desc)as category_rank
from products p
inner join orders o
    using(product_id)
group by p.product_id, p.name, p.category
order by p.category asc, total_sales desc, p.product_id asc