with fis as(select  T1.product_id, sum(T1.quantity * T2.unit_price) as total_sales, T2.unit_price, sum(T1.quantity) as total_quantity, round(sum(T1.quantity * T2.unit_price)/12,2) as avg_monthly_sales
from orders T1
join products T2
on T2.product_id = T1.product_id
where year(T1.order_date) = '2023'
group by T1.product_id),

sec as(select product_id, max(month_q) max_monthly_quantity
from 
(select sum(quantity) month_q,T1.product_id,month(T1.order_date) as month
from orders T1
where year(T1.order_date) = '2023'
group by T1.product_id,month(T1.order_date)) sum_q
group by product_id),

pre_third as(select row_number() over(partition by product_id order by sum(quantity) desc, customer_age_group ) as group_q_ranking ,product_id, customer_age_group
from (select T1.product_id , T1.quantity, case when T2.customer_age between 1 and 10 then '1-10'
when T2.customer_age between 11 and 20 then '11-20'
when T2.customer_age between 21 and 30 then '21-30'
when T2.customer_age between 31 and 40 then '31-40'
when T2.customer_age between 41 and 50 then '41-50'
when T2.customer_age between 51 and 60 then '51-60'
when T2.customer_age > 60 then '61+' end as customer_age_group
from orders T1
join customers T2
on T2.customer_id = T1.customer_id
where year(T1.order_date) = '2023') age_group
group by product_id, customer_age_group)

select T1.product_id, T2.total_sales,T2.unit_price,T2.total_quantity,T2.avg_monthly_sales,T3.max_monthly_quantity,T1.customer_age_group
from pre_third T1
join fis T2 
on T2.product_id = T1.product_id
join sec T3
on T3.product_id = T1.product_id
where T1.group_q_ranking = 1
order by T2.total_sales desc, T1.product_id