select p.product_id,
round(p.unit_price * o.total_quantity, 2) as total_sales,
round(p.unit_price, 2) as unit_price,
o.total_quantity,
round(p.unit_price * o.total_quantity / 12.0, 2) as avg_monthly_sales,
m.max_monthly_quantity,
c.customer_age_group

from products p

left join (
select product_id,
sum(quantity) as total_quantity
from orders
where left(order_date,4) = 2023
group by 1
) o
on o.product_id = p.product_id

left join (
select product_id,
max(quantity) as max_monthly_quantity
from 
(select product_id,
left(order_date,7) as month,
sum(quantity) as quantity
from orders
where left(order_date, 4) = 2023
group by 1,2) m
group by 1
) m
on m.product_id = p.product_id

left join (
select product_id, 
customer_age_group,
ROW_NUMBER() OVER (PARTITION BY product_id order by quantity desc, customer_age) as r
from (
    select o.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'
            else '61+' end as customer_age_group,
    customer_age,
    o.customer_id,
    sum(o.quantity) as quantity

    from orders o 
    left join customers c
    on o.customer_id = c.customer_id
    where left(o.order_date,4) = 2023
    group by 1,2,3,4) c 
    ) c
on c.product_id = p.product_id
and c.r = 1

order by 2 desc,1