select
        one.product_id,
        one.total_sales,
        one.unit_price,
        one.total_quantity,
        one.avg_monthly_sales,
        one.max_monthly_quantity,
        two.customer_age_group
from (
    select
        od.product_id,
        sum(od.quantity * pd.unit_price) as total_sales,
        pd.unit_price,
        sum(od.quantity) as total_quantity,
        round(sum(od.quantity * pd.unit_price) / 12,2) as avg_monthly_sales,
        max(quantity) as max_monthly_quantity
    from 
            orders as od
    left join 
            products as pd
    on 
            od.product_id = pd.product_id
    left join 
            customers as c
    on 
            c.customer_id = od.customer_id
    group by 
            od.product_id
) one
left join (
    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'
            when customer_age > 60 then '61+'
        end as customer_age_group
    from (
        select
            product_id,
            customer_age,
            row_number() over(partition by product_id order by quantity desc ,customer_age) as rn
        from 
                orders as od
        left join 
                customers as ct
        on 
                od.customer_id = ct.customer_id
    ) as a
    where 
            rn = 1
) two
on 
        one.product_id = two.product_id
order by 
        one.total_sales desc,
        one.product_id