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

with temp as (
    select
    p.name as product_name,
    sum(o.quantity) as total_sales,
    p.category,
    row_number() over(partition by p.category order by sum(o.quantity) desc , p.product_id asc) as category_rank
    from products p 
    inner join orders o 
    on o.product_id = p.product_id
    group by p.name , p.category , p.product_id
)
select 
    product_name,
    total_sales,
    category_rank
    from temp
    ORDER BY 
    category ASC, 
    total_sales DESC;