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

用了窗口函数，其实不知道怎么写出来的