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

WITH order_full_cte AS (
    SELECT
        *
    FROM
        orders o
        LEFT JOIN customers c USING (customer_id)
        LEFT JOIN products p USING (product_id)
)

SELECT
    o1.product_id,
    SUM(o1.quantity * o1.unit_price) AS total_sales,
    o1.unit_price,
    SUM(o1.quantity) AS total_quantity,
    ROUND(SUM(o1.quantity * o1.unit_price) / 12, 2) AS avg_monthly_sales,

    -- 每个产品的最大月销量
    (
        SELECT
            MAX(monthly_quantity)
        FROM (
            SELECT
                SUM(o2.quantity) AS monthly_quantity
            FROM
                order_full_cte o2
            WHERE
                o2.product_id = o1.product_id
            GROUP BY
                MONTH(o2.order_date)
        ) m
    ) AS max_monthly_quantity,

    -- 年购买量最高的客户；若并列，取年龄最小者
    (
        SELECT
            CASE
                WHEN c2.customer_age BETWEEN 1 AND 10 THEN '1-10'
                WHEN c2.customer_age BETWEEN 11 AND 20 THEN '11-20'
                WHEN c2.customer_age BETWEEN 21 AND 30 THEN '21-30'
                WHEN c2.customer_age BETWEEN 31 AND 40 THEN '31-40'
                WHEN c2.customer_age BETWEEN 41 AND 50 THEN '41-50'
                WHEN c2.customer_age BETWEEN 51 AND 60 THEN '51-60'
                ELSE '61+'
            END
        FROM (
            SELECT
                o2.customer_id,
                SUM(o2.quantity) AS yearly_qty
            FROM
                order_full_cte o2
            WHERE
                o2.product_id = o1.product_id
            GROUP BY
                o2.customer_id
        ) y
        JOIN order_full_cte c2
            ON c2.customer_id = y.customer_id
           AND c2.product_id = o1.product_id
        ORDER BY
            y.yearly_qty DESC,
            c2.customer_age ASC
        LIMIT 1
    ) AS customer_age_group

FROM
    order_full_cte o1
GROUP BY
    o1.product_id

ORDER BY
    total_sales DESC,
    o1.product_id ASC;