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

with tab as(
select o.customer_id,
case when c.customer_age>=1 and c.customer_age<=10 then '1-10'
when c.customer_age>=11 and c.customer_age<=20 then '11-20'
when c.customer_age>=21 and c.customer_age<=30 then '21-30'
when c.customer_age>=31 and c.customer_age<=40 then '31-40'
when c.customer_age>=41 and c.customer_age<=50 then '41-50'
when c.customer_age>=51 and c.customer_age<=60 then '51-60'
else '61+' end as customer_age_group,
o.product_id,p.unit_price,o.quantity,o.order_date,month(o.order_date) as months,p.unit_price*o.quantity as profit
from orders as o
inner join products as p
on o.product_id = p.product_id
inner join customers as c
on o.customer_id = c.customer_id
where year(o.order_date)='2023'
order by product_id asc
)
select *
from(
select product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,case when monthly_quantity_rnk =1 then monthly_quantity end as max_monthly_quantity,case when age_group_quantity_rnk=1 then customer_age_group end as customer_age_group
from(
select product_id,total_sales,unit_price,total_quantity,round(avg_monthly_sales,2)as avg_monthly_sales,monthly_quantity,age_group_quantity,customer_age_group,rank() over(partition by product_id order by monthly_quantity desc) as monthly_quantity_rnk,rank() over(partition by product_id order by age_group_quantity desc,customer_age_group asc) as age_group_quantity_rnk
from(
select 
product_id,sum(profit) over(partition by product_id) as total_sales,unit_price,sum(quantity) over(partition by product_id) as total_quantity,sum(profit/12) over(partition by product_id) as avg_monthly_sales,sum(quantity) over(partition by product_id,months) as monthly_quantity,sum(quantity) over(partition by product_id,customer_age_group) as age_group_quantity,customer_age_group
from tab 
)as temp
)as final 
)as final2
where max_monthly_quantity is not null and customer_age_group is not null
order by total_sales desc,product_id asc