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

with a as (
select category,
    ifnull(count(distinct oi.order_id),0) orders_cnt,
    ifnull(count(distinct buyer_id),0) buyers_cnt,
    ifnull(sum(qty),0) items_qty,
    ifnull(sum(qty*price),0) revenue,
    round(ifnull(sum(qty*price)/count(distinct oi.order_id),0),2) avg_order_value 
from order_items oi
inner join orders o on oi.order_id=o.order_id
right join product p on p.product_id=oi.product_id
where date_format(order_date,'%Y%m')=202408 or order_date is null
group by category) 
select category,
    orders_cnt,
    buyers_cnt,
    items_qty,
    revenue,
    avg_order_value,
    rank() over( order by revenue desc,orders_cnt desc,category asc) rank_by_revenue
from a 
order by revenue desc,orders_cnt desc,category asc