题解 | 统计每个产品的销售情况 - 子查询加加加加到厌倦

with
    t as (
        select
            t1.product_id,
            t1.order_id,
            t1.customer_id,
            t1.quantity,
            t1.order_date,
            month (t1.order_date) as order_month,
            t2.unit_price,
            case
                when (t3.customer_age >= 1)
                and (t3.customer_age <= 10) then '1-10'
                when (t3.customer_age >= 11)
                and (t3.customer_age <= 20) then '11-20'
                when (t3.customer_age >= 21)
                and (t3.customer_age <= 30) then '21-30'
                when (t3.customer_age >= 31)
                and (t3.customer_age <= 40) then '31-40'
                when (t3.customer_age >= 41)
                and (t3.customer_age <= 50) then '41-50'
                when (t3.customer_age >= 51)
                and (t3.customer_age <= 60) then '51-60'
                when (t3.customer_age >= 61) then '61+'
            end as customer_age_group
        from
            orders t1
            left join products t2 on t1.product_id = t2.product_id
            left join customers t3 on t1.customer_id = t3.customer_id
    ),
    m as (
        select
            product_id,
            order_month,
            sum(quantity) as month_quantity
        from
            t
        group by
            product_id,
            order_month
    ),
    a2 as (
        select
            product_id,
            sum(quantity) as q,
            customer_age_group
        from
            t
        group by
            product_id,
            customer_age_group
    ),
    a3 as (
        select
            *,
            row_number() over (
                partition by
                    product_id
                order by
                    q desc,
                    customer_age_group
            ) as rn
        from
            a2
    ),
    a as (
        select
            product_id,
            customer_age_group
        from
            a3
        where
            rn = 1
    )
select
    b.*,
    a.customer_age_group
from
    (
        select
            a1.*,
            max(m.month_quantity) as max_monthly_quantity
        from
            (
                select
                    t.product_id,
                    sum(t.quantity * t.unit_price) as total_sales,
                    t.unit_price,
                    sum(t.quantity) as total_quantity,
                    round(sum(t.quantity * t.unit_price) / 12, 2) as avg_monthly_sales
                from
                    t
                group by
                    t.product_id
            ) a1
            left join m on a1.product_id = m.product_id
        group by
            a1.product_id
    ) b
    left join a on b.product_id = a.product_id
order by
    b.total_sales desc,
    product_id