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

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