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

 # 产品ID（product_id）：产品的ID。
# 总销售额（total_sales）：该产品的2023年总销售额。
# 总销售额 = 总销量 × 单价
# 单价（unit_price）：产品的单价。
# 总销量（total_quantity）：该产品的2023年总销售数量。
# 月平均销售额（avg_monthly_sales）：2023年该产品的月均销售额。
# 月平均销售额 = 总销售额 / 12
# 单月最高销量（max_monthly_quantity）：2023年该产品的最大月销售数量。
# 先算月度表，再汇总年度表
WITH
    base AS (
        SELECT
            o.product_id,
            o.quantity,
            p.unit_price,
            DATE_FORMAT(o.order_date, '%Y-%m') AS dt,
            CASE
                WHEN c.customer_age BETWEEN 1 AND 10  THEN '1-10'
                WHEN c.customer_age BETWEEN 11 AND 20  THEN '11-20'
                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'
                WHEN c.customer_age BETWEEN 51 AND 60  THEN '51-60'
                ELSE '61+'
            END AS customer_age_group
        FROM
            orders o
            JOIN products p ON o.product_id = p.product_id
            LEFT JOIN customers c ON o.customer_id = c.customer_id
        WHERE
            o.order_date >= '2023-01-01'
            AND o.order_date < '2024-01-01'
    ),
    annual AS (
        SELECT
            product_id,
            ROUND(SUM(quantity * unit_price), 2) AS total_sales,
            MAX(unit_price) AS unit_price,
            SUM(quantity) AS total_quantity,
            ROUND(SUM(quantity * unit_price) / 12, 2) AS avg_monthly_sales
        FROM
            base
        GROUP BY
            product_id
    ),
    monthly AS (
        SELECT
            product_id,
            MAX(monthly_quantity) AS max_monthly_quantity
        FROM
            (
                SELECT
                    product_id,
                    dt,
                    SUM(quantity) AS monthly_quantity
                FROM
                    base
                GROUP BY
                    product_id,
                    dt
            ) m
        GROUP BY
            product_id
    ),
    age_rank AS (
        SELECT
            product_id,
            customer_age_group,
            ROW_NUMBER() OVER (
                PARTITION BY
                    product_id
                ORDER BY
                    SUM(quantity) DESC,
                    CASE
                        WHEN customer_age_group = '61+' THEN 61
                        ELSE CAST(
                            SUBSTRING_INDEX(customer_age_group, '-', 1) AS UNSIGNED
                        )
                    END ASC
            ) AS rn
        FROM
            base
        GROUP BY
            product_id,
            customer_age_group
    )
SELECT
    a.product_id,
    a.total_sales,
    a.unit_price,
    a.total_quantity,
    a.avg_monthly_sales,
    m.max_monthly_quantity,
    r.customer_age_group
FROM
    annual a
    LEFT JOIN monthly m ON a.product_id = m.product_id
    LEFT JOIN age_rank r ON a.product_id = r.product_id
    AND r.rn = 1
ORDER BY
    a.total_sales DESC,
    a.product_id ASC;