with t as (
    select a.product_id, unit_price*quantity as sales, unit_price, quantity, order_date,
           case when customer_age>=1 and customer_age<=10 then '1-10'
                when customer_age>=11 and customer_age<=20 then '11-20'
                when customer_age>=21 and customer_age<=30 then '21-30'
                when customer_age>=31 and customer_age<=40 then '31-40'
                when customer_age>=41 and customer_age<=50 then '41-50'
                when customer_age>=51 and customer_age<=60 then '11-20'
                else '61+' end as age_cut
    from orders a
    join customers b on a.customer_id=b.customer_id
    join products c on a.product_id=c.product_id
    where year(order_date)=2023
), t1 as (
    select product_id,
           sum(sales) over (partition by product_id) as total_sales,
           unit_price,
           sum(quantity) over (partition by product_id) as total_quantity,
           quantity,
           order_date,
           age_cut
    from t
), temp as (
    select product_id, total_sales, unit_price, total_quantity,
           round(total_sales/12, 2) as avg_monthly_sales,
           month(order_date) as month,
           sum(quantity) over (partition by product_id, month(order_date)) as sum_qty_pro_month,
           sum(quantity) over (partition by product_id, age_cut) as sum_qty_pro_agecut,
           age_cut
    from t1
), t_customer_age_group as (
    select product_id, age_cut as customer_age_group
    from (
        select product_id, age_cut, case when sum_qty_pro_agecut=max_sum_qty_pro_agecut then 1 else 0 end as flag
        from (
            select product_id, sum_qty_pro_agecut, age_cut,
                   max(sum_qty_pro_agecut) over (partition by product_id) as max_sum_qty_pro_agecut
            from temp
        ) temp2
    ) temp3
    where flag=1
    group by product_id, age_cut
)
select temp.product_id,
       max(total_sales) as total_sales,
       max(unit_price) as unit_price,
       max(total_quantity) as total_quantity,
       max(avg_monthly_sales) as avg_monthly_sales,
       max(sum_qty_pro_month) as max_monthly_quantity,
       min(customer_age_group) as customer_age_group
from temp join t_customer_age_group age_group on temp.product_id=age_group.product_id
group by temp.product_id
order by total_sales desc, temp.product_id, customer_age_group