select product_name,total_sales,row_number() over(partition by category order by total_sales desc) as category_rank
from (select products.category
,product_name,total_sales,category_rank1 
from products join (select name as product_name,sum(quantity) as total_sales,row_number() over(partition by name  order by sum(quantity) ) as category_rank1
from products as p join orders as o on p.product_id = o.product_id
group by p.name) as test1 on products.name = test1.product_name) as test2

row_number应该也行的