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

select
    t2.name product_name,
    t2.total_sales,
    t2.category_rank
from
    (
        select
            p.name,
            p.category,
            t1.quantity total_sales,
            rank() over (
                partition by
                    p.category
                order by
                    t1.quantity desc,
                    t1.product_id
            ) category_rank
        from
            (
                select
                    product_id,
                    sum(quantity) quantity
                from
                    orders
                group by
                    product_id
            ) t1,
            products p
        where
            t1.product_id = p.product_id
    ) t2
order by
    t2.category,
    t2.total_sales desc;