select
    p.name product_name,
    sum(quantity) total_sales,
    rank()over(partition by p.category order by p.category,sum(quantity) desc,p.product_id) category_rank
from products p join orders o using(product_id)
group by p.name, p.product_id,p.category  #因为题目要求要按商品类别、还有商品id排序，所以要把这些也放到group by字段里