select 
product_name,
total_sales,
row_number()over(partition by category order by total_sales desc) as category_rank 
#排名函数，以类目分组，销售额降序
from 
(select 
category,
name as product_name, 
product_id,
sum(quantity) as total_sales
from products
join orders using(product_id)
group by 1,2,3
)t1
order by category,category_rank,product_id