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

WITH
    age_range AS (
        select
            customer_id,
            CASE
                WHEN customer_age >= 61 THEN '61+'
                WHEN customer_age >= 51 THEN '51-60'
                WHEN customer_age >= 41 THEN '41-50'
                WHEN customer_age >= 31 THEN '31-40'
                WHEN customer_age >= 21 THEN '21-30'
                WHEN customer_age >= 11 THEN '11-20'
                ELSE '1 - 10'
            END as age
        from
            customers C
    ),

    cus_age AS (
        select
            product_id,
            age AS customer_age_group,
            row_number() over (
                partition by
                    product_id
                order by
                    quantity desc,
                    customer_age
            ) as rnk
        from
            orders O
            left join customers C ON O.customer_id = C.customer_id
            left join age_range A ON O.customer_id = A.customer_id
    ),

    prod_info AS (
        select
            O.product_id,
            SUM(quantity * unit_price) AS total_sales,
            unit_price,
            SUM(quantity) AS total_quantity,
            ROUND(SUM(quantity * unit_price) / 12, 2) AS avg_monthly_sales,
            max(quantity) AS max_monthly_quantity
        from
            orders O
            left join products P ON O.product_id = P.product_id
        group by
            O.product_id,
            unit_price
    )
select
    P.product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
from
    prod_info P
    join (
        select
            *
        from
            cus_age
        where
            rnk = 1
    ) N on P.product_id = N.product_id
order by
    total_sales desc,
    P.product_id