题解 | 统计每个产品的销售情况

WITH total_orders AS (
    SELECT *,
        SUM(quantity) OVER(PARTITION BY product_id) AS total_quantity
    FROM orders
),
sum_orders AS (
    SELECT c.product_id, c.customer_id,
        (b.unit_price * total_quantity) AS total_sales,
        b.unit_price,
        c.total_quantity, c.quantity,
        ROUND((b.unit_price * total_quantity / 12), 2) AS avg_monthly_sales
    FROM total_orders AS c
    LEFT JOIN products AS b
    ON c.product_id = b.product_id
),
monthly_rank AS (
    SELECT a.product_id, a.quantity, b.customer_id, b.customer_age,
    RANK() OVER(PARTITION BY a.product_id ORDER BY a.quantity DESC, b.customer_age) AS rank_quant
    FROM sum_orders AS a
    LEFT JOIN customers AS b
    ON a.customer_id = b.customer_id
),
max_quant_cust AS (
    SELECT * FROM monthly_rank
    WHERE rank_quant = 1
)
SELECT DISTINCT a.product_id, a.total_sales, a.unit_price, a.total_quantity, a.avg_monthly_sales, 
     b.quantity AS max_monthly_quantity,
    CASE 
        WHEN b.customer_age BETWEEN 21 AND 30 THEN '21-30'
        WHEN b.customer_age BETWEEN 31 AND 40 THEN '31-40'
        ELSE NULL END AS customer_age_group
FROM sum_orders AS a
LEFT JOIN max_quant_cust AS b
ON a.product_id = b.product_id
ORDER BY total_sales DESC, product_id, customer_age_group;