/*借这道题好好体会cte，明确每一列从哪个表取更不容易出错*/
with details1 as (
    /*第一个步骤表要加with,步骤表之间用逗号连接，注意计算结果都要保留两位小数*/
select p.product_id
,round(sum(p.unit_price*o.quantity),2) as total_sales
,p.unit_price
,round(sum(o.quantity),2) as total_quantity
,round(sum(p.unit_price*o.quantity)/12,2) as avg_monthly_sales
from products p
join orders o
on p.product_id = o.product_id
where year(o.order_date) = 2023/*大条件*/
group by p.product_id,p.unit_price/*按产品id分组，但是有非聚合列，也要写上，况且这里一条id对应一个价格，一一对应，不影响*/
),

details2 as(
    select t.product_id
    ,max(t.monthly_quantity) as max_monthly_quantity /*最大月销量，按产品id分组*/
    from(
    select o.product_id
    ,month(o.order_date) as order_month
    ,round(sum(o.quantity),2) as monthly_quantity /*月销售量，按产品id和月份分组*/
    from orders o
    where year(o.order_date) = 2023
    group by o.product_id,month(o.order_date)) t
    group by t.product_id
),

details3 as (
    select tt.product_id
    ,tt.customer_age_group
    ,row_number() over(partition by tt.product_id order by tt.sum_quantity desc,tt.customer_age_group) as rk /*把内层查询信息排序，从而找出最值，每类产品购买量最多的年龄段，一个产品输出一行，只按产品id分类，不要忘记题目要求的排序规则*/
    from(
    select o.product_id
    ,case when c.customer_age >= 61 then "61+"
    when c.customer_age >= 51 then "51-60"
    when c.customer_age >= 41 then "41-50"
    when c.customer_age >= 31 then "31-40"
    when c.customer_age >= 21 then "21-30"
    when c.customer_age >= 11 then "11-20"
    else "1-10" end as customer_age_group
    ,round(sum(o.quantity),2) as sum_quantity /*每类顾客购买产品的数量，按产品id和年龄分组*/
    from customers c
    join orders o on c.customer_id = o.customer_id
    where year(o.order_date) = 2023
    and c.customer_age >= 1
    group by o.product_id,customer_age_group
    ) tt
)

select details1.product_id
,details1.total_sales
,details1.unit_price
,details1.total_quantity
,details1.avg_monthly_sales
,details2.max_monthly_quantity
,details3.customer_age_group
from details1 /*找准联接条件，不要产生冗余或者使用脏数据*/
join details2 on details1.product_id = details2.product_id
join details3 on details1.product_id = details3.product_id
where details3.rk = 1
order by total_sales desc,product_id /*主查询按照总销售额排序，总销售额相同按照产品id升序排列*/