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

with base as ( #先把基础表字段整理出来

select

od.*,

pd.unit_price,

customer_age,

case

when cu.customer_age > 60 then "61+"

when cu.customer_age > 50 then "51-60"

when cu.customer_age > 40 then "41-50"

when cu.customer_age > 30 then "31-40"

when cu.customer_age > 20 then "21-30"

when cu.customer_age > 10 then "11-20"

when cu.customer_age > 0 then "1-10"

end as age_group

from products pd

left join orders od using(product_id)

left join customers cu using(customer_id)

where year(od.order_date) = '2023'

),

t1 as( #统计当年每个产品的销量及单价（销售额最后的时候直接用销量*单价，月均/12）

select

product_id,

sum(quantity) total_quantity,

max(unit_price) unit_price

from base

group by product_id

),

t2 as(#统计每个产品当年最大的月销售量，注意用的row_number,不然出现2次最大月销售量时，最后表连接会出现重复，或者用rank的话，再group by下，去重。

select

product_id,

monthly_total_quantity

from (

select

product_id,

sum(quantity) monthly_total_quantity,

row_number()over(partition by product_id order by sum(quantity) desc ) rk

from base

group by product_id,month(order_date)

)a

where rk = 1

),

t3 as( #统计每个产品购买量最大的客户的年龄段

select

product_id,

age_group

from(

select

product_id,

age_group,

rank()over(partition by product_id order by sum(quantity) desc,age_group asc)rk

from base

group by product_id,customer_id,age_group)b

where rk = 1

)

select # 按要求输出最终结果

t1.product_id,

round(unit_price * total_quantity, 2) total_sales,

unit_price,

total_quantity,

round(unit_price * total_quantity/12,2) avg_monthly_sales,

monthly_total_quantity max_monthly_quantity,

age_group customer_age_group

from t1 join t2 using(product_id)

join t3 using(product_id)

order by total_sales desc, product_id asc