with 
tb1 as(
    select orders.product_id, sum(unit_price*quantity) as total_sales, unit_price, 
        sum(quantity) as total_quantity, round(sum(unit_price*quantity)/12, 2) 
        as avg_monthly_sales
    from orders
    left join products on orders.product_id = products.product_id #取单价
    group by orders.product_id
    order by total_sales desc, orders.product_id
),
tb2 as(
    select product_id, sum(quantity) as max_monthly_quantity,
        rank() over(
        partition by product_id
        order by sum(quantity) desc
        ) as rank_total_quantity
    from orders
    group by product_id, month(order_date)
),
tb3 as(
    select customer_id, customer_age,
    case 
        when customer_age <= 10 then '1-10'
        when customer_age <= 20 then '11-20'
        when customer_age <= 30 then '21-30'
        when customer_age <= 40 then '31-40'
        when customer_age <= 50 then '41-50'
        when customer_age <= 60 then '51-60'
        else '61+'
    end as customer_age_group
    from customers
),
tb4 as(
    select product_id, orders.customer_id, customer_age_group, 
    row_number() over(
        partition by product_id
        order by sum(quantity) desc
        ) as rank_total_quantity
    from orders
    left join tb3 on orders.customer_id = tb3.customer_id
    group by product_id, orders.customer_id
    order by product_id, orders.customer_id
)
select distinct tb1.product_id, total_sales, unit_price, total_quantity, avg_monthly_sales,  
    max_monthly_quantity, customer_age_group
from tb1
left join tb2 on tb1.product_id = tb2.product_id and tb2.rank_total_quantity = 1
left join tb4 on tb1.product_id = tb4.product_id and tb4.rank_total_quantity = 1