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

with t1 as (
    select
        o.product_id,
        year(o.order_date) as year_date,
        round(sum(o.quantity*p.unit_price),2) as total_sales,
        p.unit_price,
        sum(o.quantity) as total_quantity,
        round(sum(o.quantity*p.unit_price)/12,2) as avg_monthly_sales
    from orders o
    left join products p on o.product_id=p.product_id
    left join customers c on o.customer_id=c.customer_id
    where year(o.order_date) = '2023'
    group by o.product_id,year(o.order_date),p.unit_price
),
t2 as (
    select
        product_id,
        max(monthly_quantity) as max_monthly_quantity
    from (
        select
            product_id,
            sum(quantity) as monthly_quantity,
            month(order_date) as month_date,
            row_number() over (partition by product_id order by sum(quantity)) as rk
        from orders
        where year(order_date) = '2023'
        group by month_date,product_id
    ) t
    group by product_id
),
t3 as (
    select 
        product_id,
        age_stage as customer_age_group
    from (
        select 
            product_id
            ,sum(quantity)
            ,age_stage
            ,row_number() over (partition by product_id order by sum(quantity) desc,age_stage asc) as rk
        from (
            select 
                o.product_id,
                o.quantity,
                case
                    when c.customer_age >= 1 and c.customer_age<=10 then '1-10'
                    when c.customer_age >= 11 and c.customer_age<=20 then '11-20'
                    when c.customer_age >= 21 and c.customer_age<=30 then '21-30'
                    when c.customer_age >= 31 and c.customer_age<=40 then '31-40'
                    when c.customer_age >= 41 and c.customer_age<=50 then '41-50'
                    when c.customer_age >= 51 and c.customer_age<=60 then '51-60'
                    when c.customer_age >= 61 then '61+'
                end as age_stage
            from orders o
            left join customers c on o.customer_id=c.customer_id
        ) t
        group by age_stage,product_id
    ) t
    where rk=1
)
select 
    t1.product_id,
    t1.total_sales,
    t1.unit_price,
    t1.total_quantity,
    t1.avg_monthly_sales,
    t2.max_monthly_quantity,
    t3.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 t1.total_sales desc, t1.product_id asc

第一次感受到了写sql就是不能怕麻烦，得一步一步算