with total_sales as 
(select o.product_id,round(sum(o.quantity*p.unit_price),2) as total_sales,p.unit_price,sum(o.quantity) as total_quantity, round(sum(o.quantity*p.unit_price)/12,2) as avg_monthly_sales
from orders o 
left join products p on o.product_id=p.product_id 
where year(order_date)='2023'
group by o.product_id )  ,
maxmonth as(
select product_id, date_format(order_date, '%Y-%m') as month ,sum(quantity) as monthly_quantity
from orders
group by product_id,month  ),
max_monthly_quantity as (select product_id, max(monthly_quantity) as max_monthly_quantity 
from maxmonth
group by product_id),
mag as (select o.product_id, sum(o.quantity) as agegroup_sales,case  
     when c.customer_age between 1 and 10  then '1-10'
            when c.customer_age between 11 and 20 then '11-20'
            when c.customer_age between 21 and 30 then '21-30'  
            when c.customer_age between 31 and 40 then '31-40' 
            when c.customer_age between 41 and 50 then '41-50'
            when c.customer_age between 51 and 60 then '51-60'
            when c.customer_age >= 61 then '61+' end as customer_age_group
     from orders o 
     left join customers c on o.customer_id=c.customer_id
     group by o.product_id, customer_age_group),
age as (select product_id,customer_age_group
        from(
            select  product_id,  customer_age_group ,row_number()over(partition by product_id order by agegroup_sales desc,customer_age_group ASC ) as rk
            from mag
            ) agerk
        where rk=1
        )
select 
t.product_id,
t.total_sales,
t.unit_price,
t.total_quantity,
t.avg_monthly_sales,
m.max_monthly_quantity,
a.customer_age_group
from total_sales t 
left join max_monthly_quantity m on t.product_id=m.product_id
left join age a on a.product_id =t.product_id
order by total_sales desc, a.product_id