with tmp as(
    select 
        sum(quantity) total_sales,
        category,
        name product_name,
        p.product_id id
    from products p join orders o on p.product_id=o.product_id
    group by category,p.product_id
)

select
   product_name,
   total_sales,
   row_number() over(partition by category order by total_sales desc) category_rank
from tmp
order by category,total_sales desc,id