with t1 as ( -- 关联产品表和订单表
select p.product_id, -- 选取产品id
       sum(p.unit_price * o.quantity) total_sales, -- 计算总销售额。单价*数量
       p.unit_price, -- 产品单价
       sum(o.quantity)total_quantity, -- 计算总销量，求和
       round(sum(p.unit_price * o.quantity)/12 ,2) avg_monthly_sales #计算月平均销售额，单价*数量 /12 个月
from products p  -- 产品表取
join orders o on p.product_id = o.product_id -- 通过产品id关联
where o.order_date between '2023-01-01' and '2023-12-31' #确定时间范围
group by p.product_id  -- 按照产品id分组
    ),
t2 as (   -- 求出最大月的销售数量（单月最高销售数量）
select product_id,
       max(sum_q) max_monthly_quantity  -- 找到每月最大销售额，就是最高销售月
from (select product_id,
             year(order_date),-- 拆分日期字段
             month(order_date),
             sum(quantity) sum_q -- 求出销量总和
      from orders
      where year(order_date) = 2023 -- 范围要再2023年
      group by product_id, -- 根据产品id，月，分组，年
            year(order_date),
            month(order_date)
      )a
group by product_id -- 外层则是，筛选出每个月的销售量以后，用max找出最大月，根据产品id分组
    ),
t3 as ( -- case when 求出年龄范围区间，然后sum求出
    select o.product_id,
    case when c.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,
        sum(quantity) s -- 计算每个产品+年龄组的总销量
        from customers c
        join orders o on c.customer_id = o.customer_id
        group by product_id,customer_age_group
),
t4 as (  -- 根据商品id开窗，排名选出
    select product_id,
           customer_age_group,-- 选取年龄组
           s,-- 选取年龄组的销量
           -- 窗口函数：按产品分组，先按销量降序排，销量相同时按年龄组升序排，标记排名
          rank() over (partition by product_id order by s desc,
               customer_age_group asc) rk
    from t3 )
select t1.product_id,
       t1.total_sales,
       t1.unit_price,
       t1.total_quantity,
       t1.avg_monthly_sales,
      t2.max_monthly_quantity,
       t3.customer_age_group
 from t1
join t2 on t1.product_id = t2.product_id
join t3 on t1.product_id = t3.product_id
join t4 on t1.product_id = t4.product_id
and t3.customer_age_group = t4.customer_age_group -- 关联t4，匹配产品ID+年龄组
where rk = 1 -- 只保留排名第一（销量最高）的年龄组
order by total_sales desc ,t1.product_id asc; -- 按总销售额降序排，销售额相同时按产品ID升序排