select name product_name,sum(quantity) total_sales,rank()over(partition by category order by sum(quantity)desc) category_rank
from products a
join orders b on a.product_id = b.product_id
group by product_name,category

分组简单操作