select t3.product_id product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity,customer_age_group
from
(select product_id,
    round(sum(m_quantity*unit_price),2) total_sales,
    round(unit_price,2) unit_price,
    sum(m_quantity) total_quantity,
    round(sum(m_quantity*unit_price)/12,2) avg_monthly_sales,
    max(m_quantity) max_monthly_quantity
from(
    select p.product_id product_id, p.unit_price unit_price, sum(quantity) m_quantity
    from orders o left join products p on o.product_id = p.product_id
    group by product_id, unit_price, month(order_date)
) as t1 
group by product_id,unit_price
) t3
join
(select product_id, customer_age_group
from(
select o.product_id product_id, 
    row_number() over(partition by product_id order by quantity desc) rk,
    if(customer_age between 1 and 10,'1-10',if(customer_age between 11 and 20,'11-20',if(customer_age between 21 and 30,'21-30',if(customer_age between 31 and 40,'31-40',if(customer_age between 41 and 50,'41-50',if(customer_age between 51 and 60,'51-60','61+')))))) customer_age_group
from customers c join orders o on c.customer_id = o.customer_id
) as t2
where rk = 1
) t4
on t3.product_id = t4.product_id
order by total_sales desc,product_id