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

--简单题



select 
p.category,
count(distinct os.order_id) as orders_cnt,
count(distinct od.buyer_id) as buyers_cnt,
sum(qty) as items_qty,
sum(qty*price) as revenue,
round(sum(qty*price)/count(distinct os.order_id),2) as avg_order_value,
rank()over(order by  sum(qty*price) desc) as rank_by_revenue
from order_items os inner join product p 
on os.product_id=p.product_id
inner join orders od on od.order_id=os.order_id
where
date_format(order_date,'%Y%m') =202408
group by p.category