with
    temp0 as (
        select
            product_id,
            date_format(order_date, "%Y-%m") as date,
            sum(quantity) as monthly_quantity
        from
            orders
        where
            year(order_date) = 2023
        group by
            product_id,
            date_format(order_date, "%Y-%m")
    ),
    temp1 as (
        select
            product_id,
            max(monthly_quantity) as max_monthly_quantity
        from
            temp0
        group by
            product_id
    ),
    temp2 as (
        select
            customer_id,
            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 customer_age_group,
            customer_age
        from
            customers c
    ),
    temp3 as (
        select
            o.product_id,
            sum(quantity) as total_quantity,
            sum(quantity * unit_price) as total_sales,
            round(sum(quantity * unit_price) / 12, 2) as avg_monthly_sales,
            max_monthly_quantity
        from
            orders o
            inner join products p on o.product_id = p.product_id
            inner join customers c on c.customer_id = o.customer_id
            inner join temp1 t1 on t1.product_id = o.product_id
        where
            year(order_date) = 2023
        group by
            o.product_id
    ),
    temp4 as (
        select
            o.product_id,
            t2.customer_age_group,
            sum(quantity) as customer_age_group_count,
            min(customer_age) as min_customer_age
        from
            orders o
            inner join temp2 t2 on o.customer_id = t2.customer_id
        group by
            o.product_id,
            t2.customer_age_group
    ),
    temp5 as (
        select
            product_id,
            customer_age_group,
            rank() over (
                partition by
                    product_id
                order by
                    customer_age_group_count desc,
                    min_customer_age asc
            ) as rk
        from
            temp4 t4
    ),
    temp6 as (
        select
            t5.product_id,
            t3.total_sales,
            unit_price,
            total_quantity,
            avg_monthly_sales,
            max_monthly_quantity,
            customer_age_group
        from
            temp5 t5
            inner join temp3 t3 on t5.product_id = t3.product_id
            inner join products p on p.product_id = t3.product_id
        where
            rk = 1
    )
select
    *
from
    temp6
order by
    total_sales desc,
    product_id asc;