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

with t1 as (
    # 求出产品ID、总销售额、单价、总销量、月平均销售额
    select 
        o.product_id as product_id,
        sum(quantity*unit_price) as total_sales,
        unit_price,
        sum(quantity) as total_quantity,
        round(sum(quantity*unit_price)/12, 2) as avg_monthly_sales
    from 
        orders o 
        join products p on o.product_id = p.product_id
    where 
        order_date between '2023-01-01' and '2023-12-31'
    group by 
        product_id
),
quantity_monthly_max_diffpro  as (
    # 统计单月最高销量
    # 对不同产品：先按月统计每个月的销量，再得到一年中单月最高销量(测试用例中每个月只有一个订单，存在局限)
    select 
        product_id,
        max(quantity_monthly_onepro) as max_monthly_quantity
    from (
        select 
            product_id,
            month(order_date),
            sum(quantity) as quantity_monthly_onepro
        from    
            orders
        where 
            order_date between '2023-01-01' and '2023-12-31'
        group by 
            product_id,
            month(order_date)
    ) as quantity_monthly
    group by 
        product_id
),
t2 as (
    # 求每种产品购买量最大的客户
    select 
        product_id,
        customer_id
    from (
        select 
            product_id,
            quantity_amount.customer_id,
            row_number() over(partition by product_id order by total_quantity_onecus desc, customer_age asc) as rk
        from (
            select
                product_id,
                customer_id,
                sum(quantity) as total_quantity_onecus
            from 
                orders
            group by 
                product_id,
                customer_id
        ) as quantity_amount
        left join customers on quantity_amount.customer_id = customers.customer_id
        group by 
            product_id,
            quantity_amount.customer_id
    )as quantity_rank
    where rk = 1
),
t3 as (
    # t2表已求出每种产品购买量最大的客户，此时只需求出他的age即可
    select 
        product_id,
        t2.customer_id,
        customer_age
    from 
        t2
        left join customers on t2.customer_id = customers.customer_id
),
t4 as (
    # 将年龄转换为年龄段
    select 
        product_id,
        (
            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 customer_age_group
    from t3
)

#最后拼接表
select 
    t1.product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
from 
    t1
    join quantity_monthly_max_diffpro on t1.product_id = quantity_monthly_max_diffpro.product_id
    join t4 on t1.product_id = t4.product_id
order by 
    total_sales desc, 
    t1.product_id asc