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

with t2 as(
    select
        o.*,
        c.customer_age,
        month(o.order_date) as order_month
    from orders o left join customers c
    on o.customer_id=c.customer_id
    left join products p on o.product_id=p.product_id
            )

select
    t1.product_id,
    t1.total_sales,
    t1.unit_price,
    t1.total_quantity,
    round(t1.avg_monthly_sales,2) as avg_monthly_sales,
    t6.max_monthly_quantity,
    case
        when t7.customer_age between 1 and 10 then '1-10'
        when t7.customer_age between 11 and 20 then '11-20'
        when t7.customer_age between 21 and 30 then '21-30'
        when t7.customer_age between 31 and 40 then '31-40'
        when t7.customer_age between 41 and 50 then '41-50' 
        when t7.customer_age between 51 and 60 then '51-60'
        end as customer_age_group

from

(select
    o.product_id,
    p.unit_price,
    sum(o.quantity*p.unit_price) as total_sales,
    sum(o.quantity) as total_quantity,
    sum(o.quantity*p.unit_price)/12 as avg_monthly_sales
 from 
    orders o left join customers c on o.customer_id=c.customer_id
    left join products p on o.product_id=p.product_id
 group by 
    o.product_id,p.unit_price
) t1

left join

(select
    t3.product_id,
    max(t3.monthly_quantity) as max_monthly_quantity
 from
    (select
        product_id,
        sum(quantity) as monthly_quantity
     from t2 group by product_id,order_month
    ) t3
 group by t3.product_id
) t6

on t1.product_id=t6.product_id

left join

(select
    t5.product_id,t5.customer_age 
 from
    (select
        t4.product_id,
        t4.customer_id,
        t4.customer_age,
        row_number() over(partition by product_id order by t4.order_sum desc,customer_age asc) as ranking 
    from
        (select
            product_id,
            customer_id,
            customer_age,
            sum(quantity) as order_sum
        from t2
        group by 
            product_id,customer_id,customer_age
        ) t4
    ) t5
 where t5.ranking=1
) t7

on t1.product_id=t7.product_id

order by t1.total_sales desc,t1.product_id asc