with t1 as (
    select product_id, date_format(order_date, '%m') as month, sum(quantity) as month_nums
    from orders
    group by product_id, month
),
t2 as (
    select t1.product_id, sum(t1.month_nums) as total_quantity, sum(t1.month_nums) * a.unit_price as total_sales,
    round(sum(t1.month_nums) * a.unit_price / 12, 2) as avg_monthly_sales, max(month_nums) as max_monthly_quantity
    from t1
    left join products as a
    on a.product_id = t1.product_id
    group by t1.product_id
),
t3 as (
    select 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
    from (
        select e.*, row_number() over(partition by product_id order by cnt DESC, customer_age) as rk
        from (
            select c.*, d.customer_age
            from customers as d
            right join (
                select product_id, customer_id, sum(quantity) as cnt
                from orders
                group by product_id, customer_id
            ) as c
            on c.customer_id = d.customer_id
        ) as e
    ) as f
    where rk = 1
)
select t2.product_id, t2.total_sales, h.unit_price, t2.total_quantity, t2.avg_monthly_sales, t2.max_monthly_quantity, h.customer_age_group
from t2
left join (
    select t3.product_id, g.unit_price, t3.customer_age_group
    from t3
    left join products as g
    on t3.product_id = g.product_id
) as h
on h.product_id = t2.product_id
order by t2.total_sales DESC, t2.product_id;