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

WITH product_sales AS (
    SELECT 
        o.product_id,
        ROUND(SUM(o.quantity * p.unit_price), 2) AS total_sales,  
        p.unit_price,
        SUM(o.quantity) AS total_quantity,
        ROUND(SUM(o.quantity * p.unit_price) / 12, 2) AS avg_monthly_sales,
        MAX(o.quantity) AS max_monthly_quantity
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN products p ON o.product_id = p.product_id
    GROUP BY o.product_id, p.unit_price  
),
product_max_sales_customers AS (
    SELECT 
        o.product_id,
        c.customer_age,
        CASE 
            WHEN c.customer_age BETWEEN 21 AND 30 THEN '21-30'
            WHEN c.customer_age BETWEEN 31 AND 40 THEN '31-40'
            WHEN c.customer_age BETWEEN 41 AND 50 THEN '41-50'
            ELSE '其他' 
        END AS customer_age_group,
        ROW_NUMBER() OVER (
            PARTITION BY o.product_id 
            ORDER BY 
                CASE WHEN o.quantity = (SELECT max_monthly_quantity FROM product_sales ps WHERE ps.product_id = o.product_id) THEN 0 ELSE 1 END,
                c.customer_age ASC
        ) AS rn
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
)
SELECT 
    ps.product_id,
    ps.total_sales,
    ps.unit_price,
    ps.total_quantity,
    ps.avg_monthly_sales,
    ps.max_monthly_quantity,
    pmsc.customer_age_group
FROM product_sales ps
JOIN product_max_sales_customers pmsc 
    ON ps.product_id = pmsc.product_id 
    AND pmsc.rn = 1 
ORDER BY 
    ps.total_sales DESC, 
    ps.product_id ASC;