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

WITH category_stats AS (
    SELECT
        p.category,
        COUNT(DISTINCT oi.order_id) AS orders_cnt,
        COUNT(DISTINCT o.buyer_id) AS buyers_cnt,
        SUM(oi.qty) AS items_qty,
        SUM(oi.qty*p.price) AS revenue
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN product p ON p.product_id = oi.product_id
    WHERE o.order_date BETWEEN '2024-08-01' AND '2024-08-31'
    GROUP BY p.category
)
SELECT
    *,
    ROUND(revenue/orders_cnt,2) AS avg_order_value,
    ROW_NUMBER() OVER (ORDER BY revenue DESC, orders_cnt DESC, category ASC) AS rank_by_revenue
FROM category_stats
ORDER BY revenue DESC, orders_cnt DESC, category ASC;