with t1 as (
    select customer_id,o.product_id product_id,quantity,order_date,unit_price
    from orders o
    left join products p
    on o.product_id = p.product_id
    where year(order_date) = '2023'
),
t2 as (
    select product_id,
        unit_price*sum(quantity) as total_sales,
        unit_price,
        sum(quantity) total_quantity,
        year(order_date) dateyear   
    from t1
    group by product_id,dateyear,unit_price
),
t3 as (
    select product_id,quantity,month(order_date),unit_price,quantity*unit_price m_price
    from t1
),
t4 as (
    select product_id,round(sum(m_price)/12,2) avg_monthly_sales
    from t3
    group by product_id
),
t5 as (
    select t2.product_id,total_sales,unit_price,total_quantity,avg_monthly_sales
    from t2 
    left join t4
    on t2.product_id = t4.product_id
),
t6 as (
    select product_id,month(order_date) as month_date,sum(quantity) as sum_m_qu,
    row_number() over(
        partition by product_id
        order by sum(quantity) desc
    ) rk
    from t1
    group by product_id,month(order_date)
),
t7 as (
    select product_id,sum_m_qu max_monthly_quantity
    from t6
    where rk = 1
),
t8 as (
    select t5.product_id,total_sales,unit_price,total_quantity,avg_monthly_sales,max_monthly_quantity
    from t5
    left join t7
    on t5.product_id = t7.product_id
),
t9 as (
    select product_id,customer_age,quantity
    from t1
    left join customers c
    on t1.customer_id = c.customer_id
),
t10 as (
    select product_id,quantity,
    case
        when customer_age>=1 and customer_age<=10 then '1-10'
        when customer_age>=11 and customer_age<=20 then '11-20'
        when customer_age>=21 and customer_age<=30 then '21-30'
        when customer_age>=31 and customer_age<=40 then '31-40'
        when customer_age>=41 and customer_age<=50 then '41-50'
        when customer_age>=51 and customer_age<=60 then '51-60'
        when customer_age>=61 then '61+'
    end as age_group
    from t9
),
t11 as (
    select product_id,age_group,sum(quantity) sum_q,
    row_number() over(
        partition by product_id
        order by sum(quantity) desc
    ) rk
    from t10
    group by age_group,product_id
),
t12 as (
    select product_id,age_group
    from t11
    where rk = 1
),
t13 as (
    select t8.*,age_group customer_age_group
    from t8 
    left join t12
    on t8.product_id = t12.product_id
)
select *
from t13
order by total_sales desc