题解 | 目标月份的品类销售简报

with a as(
    select
        category,
        count(distinct o.order_id) as orders_cnt,
        count(distinct buyer_id) as buyers_cnt,
        sum(qty) as items_qty,
        SUM(qty*price) as revenue
    from
        product p
        join order_items oi on p.product_id=oi.product_id
        join orders o on oi.order_id=o.order_id
    where substring(order_date,1,7)="2024-08"
    group by category)

select
    category,
    orders_cnt,
    buyers_cnt,
    items_qty,
    revenue,
    ROUND(revenue/orders_cnt, 2) as avg_order_value,
    rank() over( order by revenue desc,orders_cnt desc,category) as rank_by_revenue
from
    a
group by category
order by revenue desc,orders_cnt desc,category
;