with orders_2023 
as(
select t1.product_id, -- 产品的ID
sum(monthly_sales*unit_price) as total_sales, -- 该产品的2023年总销售额
max(unit_price) as unit_price, --  产品的单价 
sum(monthly_sales) as total_quantity, -- 该产品的2023年总销售数量
sum(monthly_sales*unit_price)/12 as avg_monthly_sales, -- 2023年该产品的月均销售额 
max(monthly_sales) as max_monthly_quantity 
from (select  
o.product_id, -- 产品的ID
sum(quantity) as monthly_sales, -- 产品每月总销售量
month(order_date) as order_month, -- 月份
max(unit_price) as unit_price --  产品的单价 
from orders o join products p 
on o.product_id = p.product_id 
where year(order_date) = 2023
group by o.product_id, month(order_date)  
) t1 
group by t1.product_id),   

customers_classification as ( 
select 
t2.product_id,  
case 
  when customer_age >= 1 and  customer_age <= 10 then '1-10'
  when customer_age >= 11 and customer_age <= 20 then '11-20'
  when customer_age >=21 and customer_age <= 30 then '21-30' 
  when customer_age >=31 and customer_age <=40 then '31-40' 
  when customer_age >=41 and customer_age <=50 then '41-50' 
  when customer_age >=51 and customer_age <=60 then '51-60' 
  else '61+' 
end as customer_age_level 
from 
(
select 
product_id, 
customer_age, 
rank() over(partition by product_id order by sum(quantity) desc, customer_age) as rk 
from customers c join orders o 
on c.customer_id = o.customer_id 
where year(order_date) = 2023 
group by product_id, o.customer_id, customer_age
) t2 
where rk = 1) 
select 
o.product_id, -- 产品的ID
total_sales as total_sales, -- 该产品的2023年总销售额 
unit_price, --  产品的单价 
total_quantity, -- 该产品的2023年总销售数量
round(avg_monthly_sales,2) as avg_monthly_sales, -- 2023年该产品的月均销售额 
max_monthly_quantity, -- 2023年该产品的最大月销售数量
customer_age_level as customer_age_group -- 2023年购买该产品数量最多的顾客的年龄段
from orders_2023 o join customers_classification c 
on o.product_id = c.product_id 
order by total_sales desc ;