with zw as(
    select 
    o.product_id,
    sum(unit_price*quantity) as total_sales,
    sum(quantity) as total_quantity
    from orders o 
    join products p on o.product_id = p.product_id 
    where order_date between '2023-01-01' and '2023-12-31'
    group by o.product_id
),
aa as (
    select 
    zw.product_id,
    total_sales,
    p.unit_price,
    total_quantity,
    round(total_sales/12,2) as avg_monthly_sales
    from zw 
    join products p on zw.product_id = p.product_id
),
jj as (
    select
    product_id,
    date_format(order_date,'%Y-%m') as month,
    sum(quantity) as s
    from orders
    where order_date between '2023-01-01' and '2023-12-31'
    group by product_id,month
),
da as (
    select 
    product_id,
    max(s) as max_monthly_quantity
    from jj
    group by product_id
),
yh as (
    select 
    customer_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 age
    from customers
),
fk as (
  select 
product_id,
age,
sum(quantity) as quantity
  from yh join orders o 
  on yh.customer_id =o.customer_id 
  group by product_id,age
),
 op as (
    select 
        product_id,
        age as customer_age_group
    from (
        select 
            fk.product_id,
            fk.age,
            fk.quantity,
            row_number() over (
                partition by fk.product_id 
                order by fk.quantity desc, fk.age asc
            ) as rn
        from fk
    ) t
    where rn = 1
)
select 
aa.product_id,
aa.total_sales,
aa.unit_price,
aa.total_quantity,
aa.avg_monthly_sales,
da.max_monthly_quantity,
op.customer_age_group
from aa join da on aa.product_id = da.product_id
join op on op.product_id = aa.product_id
order by aa.total_sales desc, aa.product_id asc;