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

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
JOIN product p ON os.product_id = p.product_id
JOIN orders od ON od.order_id = os.order_id
where
od.order_date BETWEEN '2024-08-01' AND '2024-08-31'
group by p.category

你要统计 “卖出去的商品”，就从 “卖出记录（order_items）” 出发；要统计 “所有商品（不管卖没卖）”，才从 “商品表（product）” 出发。