-- step1取出现有的数据
with t1 as
(select
    p.product_id,-- 产品ID
    round(sum(unit_price*quantity),2) as total_sales,-- 总销售额
    unit_price,-- 单价
    round(sum(quantity),2) as total_quantity,-- 总销量
    round(sum(unit_price*quantity)/12,2) as avg_monthly_sales-- 月平均销售额
from products p join orders o on p.product_id=o.product_id
where year(order_date)=2023
group by p.product_id,unit_price),-- 第一张表的with记得加逗号
-- step2产品单月最高销量
t2 as 
(select 
    product_id,max(monthly_quantity) as max_monthly_quantity-- 月最高销量
from(
    select product_id,
    substring(order_date,7) as month,
    sum(quantity) as monthly_quantity 
    from orders where year(order_date)=2023
    group by product_id,month)o
group by product_id),
-- step3购买量最多的客户年龄段
t3 as 
(select 
    product_id,customer_age_group,
    row_number() over(partition by product_id order by  sum_quantity desc) as rk
from(
    select product_id,round(sum(quantity),2) as sum_quantity,
        case when customer_age between 1 and 10 then '1-10'
            when customer_age between 11 and 20 then '11-20'
            when customer_age between 21 and 30 then '21-30'
            when customer_age between 31 and 40 then '31-40'
            when customer_age between 41 and 50 then '41-50'
            when customer_age between 51 and 60 then '51-60'
        else '61+' end as customer_age_group 
    from customers c join orders o on c.customer_id=o.customer_id
    where year(order_date)=2023 and customer_age>=1
    group by product_id,customer_age_group)tt)
-- step4代码结合
select 
    t1.product_id,-- 产品ID
    total_sales,-- 总销售额
    unit_price,-- 单价
    total_quantity,-- 总销量
    avg_monthly_sales,-- 月平均销售额
    max_monthly_quantity,-- 月最高销量
    customer_age_group-- 购买该产品数量最多的顾客的年龄段
from t1 join t2 on t1.product_id=t2.product_id
join t3 on t1.product_id=t3.product_id
where rk=1
order by total_sales desc,t1.product_id;