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

with
    t1 as (
        select
            p.product_id,
            round(sum(p.unit_price * o.quantity), 2) as total_sales,
            round(max(p.unit_price), 2) as unit_price,
            round(sum(o.quantity), 2) as total_quantity,
            round(sum(p.unit_price * o.quantity) / 12, 2) as avg_monthly_sales
        from
            orders o
            left join products p using (product_id)
        group by
            p.product_id
    ),
    t2 as (
        select
            o.product_id,
            c.customer_id,
            c.customer_age,
            date_format(o.order_date, '%Y-%m') as order_month,
            sum(o.quantity) over (
                partition by
                    o.product_id,
                    date_format(o.order_date, '%Y-%m')
            ) as monthly_quantity,
            sum(o.quantity) over (
                partition by
                    o.product_id,
                    o.customer_id
            ) as customer_quantity
        from
            orders o
            left join customers c using (customer_id)
    ),
    t3 as (
        select
            product_id,
            round(max(monthly_quantity), 2) as max_monthly_quantity
        from
            t2
        group by
            product_id
    ),
    t4 as (
        select
            product_id,
            customer_age,
            rnk
        from
            (
                select
                    product_id,
                    customer_id,
                    customer_age,
                    customer_quantity,
                    row_number() over (
                        partition by
                            product_id
                        order by
                            customer_quantity desc,
                            customer_age asc
                    ) as rnk
                from
                    t2
            ) t5
        where
            rnk = 1
    )
select
    t1.product_id as product_id,
    t1.total_sales as total_sales,
    t1.unit_price as unit_price,
    t1.total_quantity as total_quantity,
    t1.avg_monthly_sales as avg_monthly_sales,
    t3.max_monthly_quantity as max_monthly_quantity,
    case
        when t4.customer_age >= 1
        and t4.customer_age <= 10 then '1-10'
        when t4.customer_age >= 11
        and t4.customer_age <= 20 then '11-20'
        when t4.customer_age >= 21
        and t4.customer_age <= 30 then '21-30'
        when t4.customer_age >= 31
        and t4.customer_age <= 40 then '31-40'
        when t4.customer_age >= 41
        and t4.customer_age <= 50 then '41-50'
        when t4.customer_age >= 51
        and t4.customer_age <= 60 then '51-60'
        when t4.customer_age >= 61 then '61+'
    end as customer_age_group
from
    t1
    left join t3 using (product_id)
    left join t4 using (product_id)
order by
    total_sales desc,
    product_id asc