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