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

with
tmp as (
    select
        name,
        category,
        sum(quantity) as total_sales
    from products a
    left join orders b
        on a.product_id = b.product_id
    group by name, category
)
select
    name as product_name,
    total_sales,
    row_number() over (partition by category order by total_sales desc) as category_rank
from tmp
where total_sales>0
order by category asc, total_sales desc