题解 | 每个商品的销售总额

with t1 as(
select 
    a.product_id,
    a.name,
    sum(b.quantity) total_sales,
    a.category 
from products a join orders b on a.product_id = b.product_id
    group by a.product_id,a.name,a.category),

t2 as(
select 
    *,
    row_number() over(partition by t1.category order by t1.total_sales desc) rk
 from t1)

 select 
    t2.name as product_name,
    t2.total_sales,
    t2.rk as category_rank
  from t2 
  order by t2.category,t2.total_sales desc,t2.product_id;