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

WITH t0 AS(
SELECT
    product_id,
    MAX(month_sales) max_monthly_quantity
FROM 
    (SELECT
        product_id,
        SUBSTRING(order_date,1,7) month,
        SUM(quantity) month_sales
    FROM orders
    GROUP BY product_id, SUBSTRING(order_date,1,7))month_sales_table
GROUP BY product_id
),

t1 AS(
SELECT DISTINCT
    p.product_id,
    SUM(o.quantity * unit_price) total_sales,
    p.unit_price,
    SUM(o.quantity) total_quantity,
    ROUND(SUM(o.quantity * unit_price)/12, 2) avg_monthly_sales,
    max_monthly_quantity
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = o.product_id
JOIN t0 ON t0.product_id = p.product_id
GROUP BY p.product_id
)

SELECT
    t2.product_id,
    t2.total_sales,
    t2.unit_price,
    t2.total_quantity,
    t2.avg_monthly_sales,
    t2.max_monthly_quantity,
    CASE
    WHEN t2.customer_age BETWEEN 1 AND 10 THEN '1-10'
    WHEN t2.customer_age BETWEEN 11 AND 20 THEN '11-20'
    WHEN t2.customer_age BETWEEN 21 AND 30 THEN '21-30'
    WHEN t2.customer_age BETWEEN 31 AND 40 THEN '31-40'
    WHEN t2.customer_age BETWEEN 41 AND 50 THEN '41-50'
    WHEN t2.customer_age BETWEEN 51 AND 60 THEN '51-60'
    ELSE '61+' END customer_age_group
FROM(
    SELECT
        t1.product_id,
        t1.total_sales,
        t1.unit_price,
        t1.total_quantity,
        t1.avg_monthly_sales,
        t1.max_monthly_quantity,
        MIN(c.customer_age) customer_age
    FROM t1
    JOIN orders o ON t1.product_id = o.product_id AND o.quantity = t1.max_monthly_quantity
    JOIN customers c ON o.customer_id = c.customer_id
    GROUP BY t1.product_id
    ORDER BY t1.total_sales DESC, t1.product_id) t2

更正版，如有错误还请各位大佬指正！

原版链接：https://www.nowcoder.com/discuss/736680775428345856?sourceSSR=users