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

select
    product_name,
    total_sales,
    category_rank
from
(
select
    category,
    product_name,
    total_sales,
    rank() over(partition by category order by total_sales desc) as category_rank
from
(
select
    distinct name as product_name,
    sum(quantity) over(partition by name) as total_sales,
    category
from
(
select
    o.product_id,
    name,
    category,
    quantity
from
    orders o
    left join
    products p
    on o.product_id=p.product_id
) a
) b
where product_name is not null
order by category,total_sales desc
)c
;