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

WITH year_index AS (
    SELECT 
        p.product_id AS product_id,
        SUM(unit_price * quantity) AS total_sales,
        unit_price,
        SUM(quantity) AS total_quantity,
        SUM(unit_price * quantity) / 12 AS avg_monthly_sales
    FROM orders o 
    INNER JOIN products p ON p.product_id = o.product_id
    GROUP BY p.product_id
),

-- 单月最高销量
month_index AS (
    SELECT 
        product_id,
        MAX(month_quantity) AS max_monthly_quantity
    FROM (
        SELECT 
            product_id,
            MONTH(order_date) AS order_month,
            SUM(quantity) AS month_quantity
        FROM orders
        GROUP BY product_id, order_month
    ) AS monthly
    GROUP BY product_id
),

-- 对每个产品来说，购买的最多的数量所对应的customer_id，并选择年龄较小的顾客
max_customer AS (
    SELECT 
        o.customer_id,
        o.product_id,
        c.customer_age
    FROM orders o
    INNER JOIN customers c ON o.customer_id = c.customer_id
    WHERE (o.product_id, o.quantity) IN (
        SELECT 
            product_id,
            MAX(quantity)
        FROM orders
        GROUP BY product_id
    )
    ORDER BY o.product_id, c.customer_age
),

-- 对每个产品，选择年龄较小的顾客
selected_customer AS (
    SELECT 
        product_id,
        customer_id,
        customer_age,
        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY customer_age) AS age_rank
    FROM max_customer
),

-- 仅选择年龄最小的顾客
final_customer AS (
    SELECT 
        product_id,
        customer_id,
        customer_age
    FROM selected_customer
    WHERE age_rank = 1
),

-- 根据顾客年龄分组
age_dis AS (
    SELECT
        product_id,
        CASE 
            WHEN customer_age BETWEEN 1 AND 10 THEN '1-10'
            WHEN customer_age BETWEEN 11 AND 20 THEN '11-20'
            WHEN customer_age BETWEEN 21 AND 30 THEN '21-30'
            WHEN customer_age BETWEEN 31 AND 40 THEN '31-40'
            WHEN customer_age BETWEEN 41 AND 50 THEN '41-50'
            WHEN customer_age BETWEEN 51 AND 60 THEN '51-60'
            ELSE '61+' 
        END AS customer_age_group
    FROM final_customer
)

SELECT
    ye.product_id AS product_id,
    total_sales,
    unit_price,
    total_quantity,
    ROUND(avg_monthly_sales, 2) AS avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
FROM year_index ye
INNER JOIN month_index m ON ye.product_id = m.product_id
INNER JOIN age_dis a ON ye.product_id = a.product_id
ORDER BY total_sales DESC, product_id;