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

# 年销售总量
with t as
(select product_id
,round(sum(quantity)) total_quantity
from orders
group by product_id)
#月销售额及排序
, t1 as
(select product_id
,month(order_date) month
,sum(quantity) monthly_sales
,row_number() over(partition by product_id order by sum(quantity) desc) rn
from orders
group by product_id,month(order_date)
)
# 为每个订单的用户打标年龄段
,t2 as(
select product_id
,quantity
,case 
when customer_age<=10 then '1-10'
when customer_age<=20 then '11-20'
when customer_age<=30 then '21-30'
when customer_age<=40 then '31-40'
when customer_age<=50 then '41-50'
when customer_age<=60 then '51-60'
else '60+'
end as customer_age_group
from orders
left join customers
on orders.customer_id=customers.customer_id
)
# 为每个商品购买年龄段用户数排序
,t3 as(
select product_id
,customer_age_group
,row_number() over(partition by product_id  order by sum(quantity) desc,customer_age_group asc) age_rn
# 这里是我的错点：要求2023年购买该产品数量最多的顾客的年龄段，应该sum(quantity)每种产品的所有订单的总购买数量。而我一开始用的count（*）每种产品的订单数，即购买次数。忽略了每次可以买quantity多个。
from t2
group by product_id,customer_age_group
)

select  t.product_id product_id
,round(total_quantity*unit_price,2) total_sales
,unit_price
,total_quantity
,round(total_quantity*unit_price/12,2) avg_monthly_sales
,monthly_sales max_monthly_quantity
,t3.customer_age_group customer_age_group
from t
left join t1
on t.product_id=t1.product_id

left join t3
on t.product_id=t3.product_id

left join products
on t.product_id=products.product_id
where rn=1 and age_rn=1

order by total_sales desc,product_id

这种题就要一步一步来，思路不清就用临时表，最后join到一起。