select
product_name,
total_sales,
row_number() over(partition by category order by total_sales desc, product_id asc) as category_rank
from (
select
o.product_id,
name as product_name,
category,
sum(quantity) as total_sales
from orders o
join products p
on o.product_id = p.product_id
group by 1,2,3
# order by 1,2,3
) t
order by category asc, total_sales desc
;