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

# 用户分组
with cag as(
    select
        customer_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 customers     
),
# 解决 max_monthly_quantity列
monthly_summary as(
    select
        product_id,
        max(cnt) as max_monthly_quantity
    from(
        select
            product_id,
            sum(quantity) as cnt,
            date_format(order_date,'%Y-%m') as yearmonth,
            rank() over(
                partition by product_id
                order by sum(quantity) desc
            )as rn
        from orders
        group by product_id,date_format(order_date,'%Y-%m')
    )t 
    where t.rn=1
    group by product_id
),
# 解决customer_age_group
top_buyer_by_product as(
    select
        product_id,
        customer_age_group
    from(
        select
            o.product_id,
            o.customer_id,
            cag.customer_age_group,
            rank() over(
                partition by o.product_id
                order by o.quantity desc,o.customer_id
            )as rn
        from orders o
            left join cag
            on o.customer_id=cag.customer_id
    )t
    where t.rn=1
),
# 解决剩下的列
total_summary as(
    select
        o.product_id as product_id,
        round(sum(p.unit_price*o.quantity),2) as total_sales,
        avg(p.unit_price) as unit_price,
        sum(o.quantity) as total_quantity,
        round(sum(p.unit_price*o.quantity)/12,2) as avg_monthly_sales
    from orders o 
        left join products p 
        on o.product_id=p.product_id
    group by o.product_id
)

# 三个cte的结果join在一起
select
    ts.product_id as product_id,
    round(ts.total_sales,2) as total_sales,
    round(ts.unit_price,2) as unit_price,
    ts.total_quantity as total_quantity,
    ts.avg_monthly_sales as avg_monthly_sales,
    ms.max_monthly_quantity as max_monthly_quantity,
    t.customer_age_group as customer_age_group
from total_summary ts 
    left join monthly_summary ms 
    on ts.product_id=ms.product_id
    left join top_buyer_by_product t
    on ts.product_id=t.product_id
order by total_sales desc;