#顾客年龄分层并查询购买数量最多顾客
with t1 as (select c.customer_id,
                   customer_name,
                   product_id,
                   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 '60+'
                       end as customer_age_group
            from customers c
                     join (select product_id, customer_id
                           from (select product_id,
                                        c.customer_id,
                                        sum(quantity)                                                     as total_quantity,
                                        rank() over (partition by product_id order by sum(quantity) desc,c.customer_age ) as rk
                                 from orders o join customers c using(customer_id)
                                 group by product_id, c.customer_id) t
                           where rk = 1) mc on c.customer_id = mc.customer_id),
# 年销售额和月均销售额
     total_avg as (select product_id,
                          round(sum(quantity) * unit_price, 2)      as total_salas,
                          unit_price,
                          sum(quantity)                             as total_quantity,
                          round(sum(quantity) * unit_price / 12, 2) as avg_monthly_salas
                   from products p
                            join orders o using (product_id)
                   group by product_id),
# 产品月最大销售数量
     t2 as (select product_id, max(sum_monthly_quantity) as max_monthly_quantity
            from (select p.product_id, month(order_date) as 'month', sum(quantity) as sum_monthly_quantity
                  from orders o
                           join products p using (product_id)
                  group by p.product_id, month(order_date)) as aa
            group by product_id)

select t1.product_id,
       ta.total_salas,
       ta.unit_price,
       total_quantity,
       avg_monthly_salas,
       max_monthly_quantity,
       customer_age_group
from total_avg ta
         join t2 on ta.product_id = t2.product_id
         join t1 on t1.product_id = t2.product_id
order by total_salas desc, t1.product_id