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

WITH full_data AS (
    SELECT 
        p.category,
        COUNT(DISTINCT o.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,  
        ROUND(SUM(oi.qty * p.price) / COUNT(DISTINCT o.order_id),2) AS avg_order_value
    FROM orders o
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    LEFT 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 
    category,
    orders_cnt,
    buyers_cnt,
    items_qty,
    revenue, 
    avg_order_value,
    RANK() OVER (ORDER BY revenue DESC, orders_cnt DESC, category ASC) AS rank_by_revenue
FROM full_data
ORDER BY revenue DESC, orders_cnt DESC, category ASC;