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

with t1 as(
    select p.category,
        count(distinct oi.order_id) as orders_cnt,
        count(distinct o.buyer_id) as buyers_cnt,
        sum(qty) as items_qty,
        sum(qty*price) as revenue,
        round(sum(qty*price)/count(distinct oi.order_id),2) as avg_order_value
    from product p 
    left join order_items oi using(product_id)
    left join orders o using(order_id)
    where left(order_date,7)='2024-08'
    group by 1
),

t2 as(
    select category,
        rank()over(order by revenue desc,orders_cnt desc,category) as rank_by_revenue
    from t1
)

select t1.category,
    t1.orders_cnt,
    t1.buyers_cnt,
    t1.items_qty,
    t1.revenue,
    t1.avg_order_value,
    t2.rank_by_revenue
from t1
left join t2 using(category)
order by revenue desc,orders_cnt desc,category