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

with 
t1 as(
# 产品编号、单价、总销售量、总销售额、月平均销售额
    select
        product_id,
        unit_price,
        sum(quantity) as total_quantity,
        sum(quantity*unit_price) as total_sales,
        round(sum(quantity*unit_price)/12,2) as avg_monthly_sales
    from
        orders
        left join products using(product_id)
        left join customers using(customer_id)
    where
        date_format(order_date,'%Y')='2023'
    group by
        product_id
)
,
t2 as(
    select
        customer_id,
        (
            case
                when customer_age between 1 and 10 then 1
                when customer_age between 11 and 20 then 2
                when customer_age between 21 and 30 then 3
                when customer_age between 31 and 40 then 4
                when customer_age between 41 and 50 then 5
                when customer_age between 51 and 60 then 6
                else 7
            end
        ) as age_group_num
    from
        customers
)
,
t3 as(
    select distinct
        age_group_num,
        (
            case
                when age_group_num=1 then '1-10'
                when age_group_num=2 then '11-20'
                when age_group_num=3 then '21-30'
                when age_group_num=4 then '31-40'
                when age_group_num=5 then '41-50'
                when age_group_num=6 then '51-60'
                else '61+'
            end
        ) as age_group
    from
        t2
)
,
t4 as (
    select
        product_id,
        age_group_num,
        rank()over(partition by product_id order by sum(quantity) desc, age_group_num) as brank
    from
        orders left join t2 using(customer_id)
    group by
        product_id,
        age_group_num
)
,
t5 as(
# 销售量最大的年龄段
    select
        product_id,
        age_group as customer_age_group
    from
        t4 left join t3 using(age_group_num)
    where
        brank=1
)
,
t6 as(
    select
        product_id,
        date_format(order_date,'%Y-%m') as month_date,
        sum(quantity) as monthly_count,
        rank()over(partition by product_id order by sum(quantity) desc) as mrank
    from
        orders
    group by
        product_id,
        date_format(order_date,'%Y-%m')
)
,
t7 as(
# 月最大销售量
    select
        product_id,
        monthly_count as max_monthly_quantity
    from
        t6
    where
        mrank=1
)

select distinct
    product_id,
    total_sales,
    unit_price,
    total_quantity,
    avg_monthly_sales,
    max_monthly_quantity,
    customer_age_group
from
    t1
    left join t5 using(product_id)
    left join t7 using(product_id)
order by
    total_sales desc,
    product_id

费了好大劲。。