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

select 
    p.category,
    count(distinct o.order_id) as orders_cnt,
    count(distinct o.buyer_id) as buyers_cnt,
    sum(oi.qty) as items_qty,
    SUM(oi.qty*p.price) as revenue,
    ROUND(SUM(oi.qty*p.price)/count(distinct o.order_id), 2) as avg_order_value,
    rank() over(order by SUM(oi.qty*p.price) desc, count(distinct o.order_id) desc, category asc) as rank_by_revenue
from product p
join order_items oi on p.product_id = oi.product_id
join orders o on o.order_id = oi.order_id
where date_format(o.order_date, '%Y-%m') = '2024-08'
group by p.category