select t12.product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity,customer_age_group
from
(select product_id,customer_age_group from
(select product_id,customer_age_group,quantity,
rank()over(partition by product_id order by quantity desc,customer_age asc) as rank_age
from
(select p.product_id as product_id,
case 
when 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'
when customer_age > 60 then '61+'
else null
end as customer_age_group,quantity,customer_age
 from products p
join
(select c.customer_id,customer_age,product_id,quantity FROM customers c
join
(
select customer_id,product_id,quantity,order_date from orders
where year(order_date) = 2023
) as o
on c.customer_id = o.customer_id) as t
on p.product_id = t.product_id) as ab) as cd
where rank_age = 1) as t12
join
(select t10.product_id as product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity
from
(select product_id,max_monthly_quantity from(
select product_id,sum(quantity) as max_monthly_quantity,month(order_date),
row_number() over(partition by product_id order by sum(quantity) desc) as rank_month
 from orders
where year(order_date) = 2023
group by product_id,month(order_date)) as t2
where rank_month = 1) as t10
join
(
select product_id,round(sum(unit_price*quantity),2) as total_sales,
round(max(unit_price),2) as unit_price, sum(quantity) as total_quantity,
round(sum(unit_price*quantity)/12,2) as avg_monthly_sales
from
(select p.product_id as product_id,unit_price,customer_age,quantity from products p
join
(select c.customer_id,customer_age,product_id,quantity FROM customers c
join
(
select customer_id,product_id,quantity,order_date from orders
where year(order_date) = 2023
) as o
on c.customer_id = o.customer_id) as t
on p.product_id = t.product_id) as t1
group by product_id
) as t11
on t10.product_id = t11.product_id) as t13
on t12.product_id = t13.product_id
order by total_sales desc,t12.product_id asc