with tmp as(
    select t_o.customer_id,t_o.product_id,t_o.quantity,
    case
        when t_c.customer_age between 1 and 10  then '1-10'
        when t_c.customer_age between 11 and 20  then '11-20'
        when t_c.customer_age between 21 and 30  then '21-30'
        when t_c.customer_age between 31 and 40  then '31-40'
        when t_c.customer_age between 41 and 50  then '41-50'
        when t_c.customer_age between 51 and 60  then '51-60'
        else '61+'
    end as customer_age_group
    from orders t_o
    left join customers t_c
    on t_o.customer_id = t_c.customer_id
)
select t1.product_id,round(t2.total_quantity*t_p.unit_price,2) total_sales,
t_p.unit_price,t2.total_quantity,
round(t2.total_quantity*t_p.unit_price/12,2) avg_monthly_sales,
t1.quantity max_monthly_quantity,t1.customer_age_group
from(
select *,row_number() over(partition by product_id order by quantity desc,customer_age_group asc) as ranking
  ---row_bumer()窗口函数主要就是解决展示年纪较小的问题
from tmp
) t1,
(
select product_id,sum(quantity) total_quantity
from orders
where year(order_date)='2023'
group by product_id
) t2,products t_p
where ranking =1
and t2.product_id=t_p.product_id and t1.product_id=t2.product_id
order by total_sales desc,product_id asc