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

with t1 as (
    select
    o.product_id,
    month(o.order_date) as mon,
    p.unit_price,
    sum(o.quantity*p.unit_price) as mon_sales,
    sum(o.quantity) as mon_qty
    from orders o 
    left join products p 
    on o.product_id = p.product_id
    where year(order_date) = 2023
    group by 1,2,3
),

t2 as (
    select
    o.product_id,
    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 age_grp,
    sum(quantity) as cnt
    from orders o
    left join customers c on o.customer_id = c.customer_id
    where year(order_date) = 2023
    group by 1,2
)

select
aa.product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
age_grp as customer_age_group
from (
    select
    product_id,
    unit_price,
    sum(mon_sales) as total_sales,
    sum(mon_qty) as total_quantity,
    round(sum(mon_sales)/12, 2) as avg_monthly_sales,
    max(mon_qty) as max_monthly_quantity
    from t1 
    group by 1,2
) aa
join (
    select
    product_id,
    age_grp,
    row_number() over(partition by product_id order by cnt desc, age_grp asc) as rk
    from t2

) bb on aa.product_id = bb.product_id
where bb.rk = 1
order by total_sales desc, product_id asc
;