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

# customers customer_id
# products product_id
# orders order_id

#1.product_id
#2.product_id
#3.product_id
#4.product_id
#5.product_id
#6.product_id+month
#7.product_id+age_group


with t1 as(
    select
    p.product_id
    ,unit_price
    ,sum(quantity) as total_quantity
    ,sum(quantity)*unit_price as total_sales
    ,sum(quantity)*unit_price/12 as avg_monthly_sales
    from products p
    left join orders o
    on p.product_id = o.product_id
    where year(order_date) = 2023
    group by 1,2) #前5需求表
,t2 as(
    select
    product_id
    ,max(monthly_sales) as monthly_sales
    from
        (select
        p.product_id
        ,month(order_date) as month
        ,sum(quantity)as monthly_sales
        from products p
        left join orders o
        on p.product_id = o.product_id
        where year(order_date) = 2023
        group by 1,2)a
    group by 1) #第6需求表
,t3 as(
    select
    product_id
    ,age_group
    from
        (select
        product_id
        ,age_group
        ,age_group_quantity
        ,row_number()over(partition by product_id order by age_group_quantity desc,age_group) as rn
        from
            (select
            p.product_id 
            ,customer_age
            ,case when customer_age between 1 and 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 '61+' end  as age_group
            ,sum(quantity) as age_group_quantity
            from products p
            left join orders o
            on p.product_id = o.product_id
            left join customers c
            on o.customer_id = c.customer_id
            where year(order_date) = 2023
            group by 1,2,3)a)b
        where rn = 1) #第7需求表

select 
t1.product_id
,t1.total_sales
,t1.unit_price
,t1.total_quantity
,round(t1.avg_monthly_sales,2) as avg_monthly_sales
,round(t2.monthly_sales,2) as max_monthly_quantity
,t3.age_group as customer_age_group
from t1
left join t2
on t1.product_id = t2.product_id
left join t3
on t1.product_id = t3.product_id
order by total_sales desc,customer_age_group;