select product_name,total_sales,

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

order by category,total_sales desc

category的分类是最外层的，大于product_id分类，所以内层查询可以先计算出total_sales