select product_name,total_sales,
rank() over(partition by category order by 	total_sales desc, product_id) category_rank
from (select name product_name,sum(quantity) total_sales,category,p.product_id
from products p
inner join orders o on p.product_id=o.product_id
group by name,p.product_id,category
order by category,total_sales desc) t ;
就是最后测试的时候要把有null的部分去掉，其实SUM会过滤掉null的，主要是inner join使其不要出现了