题解 | 统计每个产品的销售情况

with t1 as (
    select p.product_id,
    unit_price,
    round(sum(quantity*unit_price),2) total_sales,
    sum(quantity) total_quantity,
    round(sum(quantity*unit_price)/12,2) avg_monthly_sales
    from orders o 
    join products p on o.product_id=p.product_id
    group by p.product_id, unit_price
),

t2 as (
    select product_id,
    mon_quantity max_monthly_quantity
    from (
        select p.product_id,
        sum(quantity) mon_quantity,
        row_number() over (partition by p.product_id order by sum(quantity) desc) rk
        from orders o
        join products p using (product_id)
        group by p.product_id, month(order_date)
    ) a1
    where rk=1
),

t3 as (
    select product_id, 
    (case when customer_age<=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 '60+' end) customer_age_group
    from (
        select product_id, 
        o.customer_id,  
        row_number() over (partition by product_id order by sum(quantity) desc, customer_age) rk
        from orders o
        join customers c on o.customer_id=c.customer_id   
        group by product_id, customer_id
    ) a2
    join customers c on a2.customer_id=c.customer_id
    where rk=1
)

select t1.product_id,
total_sales,
unit_price,
total_quantity,
avg_monthly_sales,
max_monthly_quantity,
customer_age_group
from t1
join t2 on t1.product_id=t2.product_id
join t3 on t1.product_id=t3.product_id
order by total_sales desc, product_id