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

select
*
,rank()over(order by revenue desc,orders_cnt desc,category ) rank_by_revenue
from (
    select
    category
    ,count(distinct oi.order_id) orders_cnt
    ,count(distinct buyer_id ) buyers_cnt
    ,sum(qty) items_qty
    ,SUM(qty*price) revenue
    ,ROUND(SUM(qty*price)/count(distinct oi.order_id), 2) avg_order_value
    from order_items oi left join orders on orders.order_id = oi.order_id 
    left join product on product.product_id = oi.product_id 
    where order_date between '2024-08-01' and '2024-08-31'
    group by 1
) t1
order by 5 desc,2 desc,1