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

with date_rank as(  -- 每个产品23年每个月的销量
    select
        product_id
        ,month(order_date) as month
        ,sum(quantity) as month_cnt
    from
        orders
    where
        year(order_date)='2023'
    group by
        product_id,month(order_date)
)
,user_rank as(  -- 每个产品每个用户的购买量
    select
        product_id
        ,customer_id
        ,sum(quantity) as user_cnt
    from
        orders 
    where
        year(order_date)='2023'
    group by
        product_id,customer_id
)
,max_date as( -- 每个产品每个月份的销量和排名
    select
        row_number()over(partition by product_id order by month_cnt desc) as rk
        ,month
        ,product_id
        ,month_cnt
    from
        date_rank


)
,max_age as( -- 每个产品每个用户的购买量和排名、年龄
    select
        row_number()over(partition by product_id order by user_cnt desc,customer_age) as rk
        ,customer_id
        ,product_id
        ,customer_age
        ,user_cnt
    from
        user_rank left join customers using(customer_id)

)

select
    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
    ,md.month_cnt as max_monthly_quantity
    ,case when ma.customer_age >1 and ma.customer_age <=10 then '1-10'
            when ma.customer_age >10 and ma.customer_age <=20 then '11-20'
            when ma.customer_age >20 and ma.customer_age <=30 then '21-30'
            when ma.customer_age >30 and ma.customer_age <=40 then '31-40'
            when ma.customer_age >40 and ma.customer_age <=50 then '41-50'
            when ma.customer_age >50 and ma.customer_age <=60 then '51-60'
            else '60+' end as customer_age_group
from
    orders o
    left join customers c using(customer_id)
    left join products p using(product_id)
    left join max_date md using(product_id)
    left join max_age ma using(product_id)
where   
    year(o.order_date)='2023' and md.rk=1 and ma.rk=1
group by
    product_id,md.month_cnt,ma.customer_age
order by
    total_sales desc
    ,product_id asc