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

select 
*
,round(revenue / buyers_cnt , 2) avg_order_value 
,rank()over(order by revenue desc) rank_by_revenue 
from 
(    select 
    category    
    ,count(distinct b.order_id) orders_cnt
    ,count(distinct b.buyer_id) buyers_cnt 
    ,sum(qty) items_qty 
    ,sum(qty * price) revenue
    from order_items c left join orders b 
    on b.order_id = c.order_id 
    left join product a 
    on a.product_id = c.product_id
    where left(order_date  , 7) = '2024-08'
    group by 1
) a
order by rank_by_revenue