# 查询2023年每个产品的以下信息：产品ID、总销售额、单价、总销量、月均销售额、单月最高销量、购买数量最多的顾客年龄段
with xiaoshoue as
(
select a1.product_id,sum(a2.quantity)*a1.unit_price as total_sales,
a1.unit_price,sum(a2.quantity) as total_quantity,
(sum(a2.quantity)*a1.unit_price)/12 as avg_monthly_sales
from 
(select product_id,unit_price
from products
) as a1
left outer join
orders as a2 on a1.product_id=a2.product_id
group by a1.product_id
),

max_sales as 
(
select h.product_id,h.quantity as max_monthly_quantity,b.customer_age as customer_age_group
from
(select h1.order_id,h1.customer_id,h1.product_id,h1.quantity
from
(select product_id,customer_id,order_id,quantity,
row_number()over(partition by product_id order by quantity desc) as rnk
from orders
) as h1
where h1.rnk=1
) as h
left outer join
customers as b on h.customer_id=b.customer_id
)

select c.product_id,c.total_sales,c.unit_price,c.total_quantity,
round(c.avg_monthly_sales,2) as avg_monthly_sales,d.max_monthly_quantity,
case when d.customer_age_group between 1 and 10 then '1-10'
when d.customer_age_group between 11 and 20 then '11-20'
when d.customer_age_group between 21 and 30 then '21-30'
when d.customer_age_group between 31 and 40 then '31-40'
when d.customer_age_group between 41 and 50 then '41-50'
when d.customer_age_group between 51 and 60 then '51-60'
else '61+' end as customer_age_group
from xiaoshoue as c
inner join max_sales as d on c.product_id=d.product_id
order by c.total_sales desc,c.product_id asc