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

with t1 as (
select o.product_id,
sum(quantity) *p.unit_price total_sales,
unit_price,sum(quantity) total_quantity,
round(sum(quantity) *p.unit_price/12,2) avg_monthly_sales
# row_number() over(partition by p.product_id order by sum(quantity) desc) rn
from orders o
join products p on o.product_id = p.product_id
group by o.product_id
)
,t2 as (-- 各产品的各月销量及其次序表
select o.product_id,
# month(order_date) month,
sum(quantity) monthly_quantity,
row_number() over(partition by o.product_id order by sum(quantity) desc) rn
from orders o 
join products p on o.product_id = p.product_id
group by o.product_id,month(order_date)
order by o.product_id,month(order_date)
)
,t3 as (-- 各产品的消费者购买量次序表
select o.product_id,
# o.customer_id,
customer_age,
# sum(quantity),
row_number() over(partition by o.product_id order by sum(quantity) desc,customer_age) crn
from orders o
join customers c on o.customer_id = c.customer_id
group by o.product_id,o.customer_id
order by o.product_id,o.customer_id
)

select t1.product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
t2.monthly_quantity max_monthly_quantity,
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'
else '61+'
end as customer_age_group
from t1
join t2 on t1.product_id = t2.product_id
join t3 on t1.product_id = t3.product_id
where t2.rn =1 and t3.crn =1
order by total_sales desc,
product_id asc