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

- 奇怪的bug，不能用left或者right join，直接用join反而可以过