select name product_name,
sum(quantity) total_sales,
row_number() over (partition by category order by sum(quantity) desc,product_id) category_rank
from products join orders using(product_id)
group by category,name,product_id
order by category

窗口函数中设置每个窗口内部的排序规则，可以有多个