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

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

使用with可以使代码逻辑步骤更清晰，参考官方解法