select name as product_name , total_sales
       ,row_number()over(partition by category order by total_sales desc ,product_id) as category_rank
       from (
    select t1.product_id , name, category , sum(quantity) as total_sales
    from orders t1 
    inner join products t2 on t1.product_id = t2.product_id
    group by t1.product_id , name, category 
       )t
order by category,total_sales desc

开窗