select product_name, total_sales,
row_number() over (partition by category order by total_sales desc, pro2.product_id) as category_rank
from (
    select name as product_name, sum(quantity) as total_sales
    from products pro join orders ord on pro.product_id=ord.product_id
    group by name
) temp join products pro2 on temp.product_name=pro2.name
order by category, total_sales desc