with t1 as(
    select 
        cu.customer_id,
        customer_age,
        order_id,
        product_id,
        quantity,
        order_date
    from
        customers cu left join orders ord on cu.customer_id = ord.customer_id
)
,
t2 as (
    select 
        customer_id,
        customer_age,
        order_id,
        t1.product_id,
        product_name,
        unit_price,
        quantity,
        order_date
    from 
        t1 left join products pro on t1.product_id = pro.product_id
)
,
t4 as (
    select 
        product_id,
        customer_id,
        customer_age,
        all_quan,
        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 
            product_id,
            customer_id,
            customer_age,
            sum(quantity) as all_quan,
            ROW_NUMBER() over (partition by product_id order by sum(quantity)desc , customer_age asc) as rk
        from 
            t2 
        group by 
            product_id,customer_id,customer_age
    ) as t3
    where rk = 1
)
,
t5 as (
    select 
        product_id,
        Round(sum(quantity)*avg(unit_price),2) as total_sales,
        Round(avg(unit_price),2) as unit_price,
        Round(sum(quantity),2) as total_quantity,
        Round((sum(quantity)*avg(unit_price)/12),2) as avg_monthly_sales
    from
        t2
    group by 
        product_id
) 

select 
    t5.product_id,
    t5.total_sales,
    t5.unit_price,
    t5.total_quantity,
    t5.avg_monthly_sales,
    t4.all_quan as max_monthly_quantity,
    t4.customer_age_group
from 
    t5 left join t4 on t5.product_id = t4.product_id
order by t5.total_sales desc, t5.product_id asc