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

WITH sub AS(
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 JOIN orders o on oi.order_id = o.order_id
JOIN product p ON oi.product_id = p.product_id
WHERE MONTH(order_date) = 8
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)rank_by_revenue
FROM sub
ORDER BY revenue DESC, orders_cnt DESC, category