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

#前5个字段
with t1 as(
    select p.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 products p
    join orders o on p.product_id=o.product_id
    group by p.product_id
),
#月最高销量
t2 as(
    select product_id,
        max(m_quantity) as max_monthly_quantity
    from(
        select product_id,
            sum(quantity) as m_quantity
        from orders
        group by product_id,month(order_date)
    )a 
    group by product_id
),
#计算每个客户购买量，并排名
t3 as(
    select product_id,
        b.customer_id,
        customer_age,
        row_number()over(partition by product_id order by c_quantity desc,customer_age) as rn
    from(
        select product_id,
            customer_id,
            sum(quantity) as c_quantity
        from orders
        group by product_id,customer_id
    )b
    join customers c on b.customer_id=c.customer_id
)

select t1.product_id,
    t1.total_sales,
    t1.unit_price,
    t1.total_quantity,
    t1.avg_monthly_sales,
    t2.max_monthly_quantity,
    case when t3.customer_age between 1 and 10 then '1-10'
        when t3.customer_age between 11 and 20 then '11-20'
        when t3.customer_age between 21 and 30 then '21-30'
        when t3.customer_age between 31 and 40 then '31-40'
        when t3.customer_age between 41 and 50 then '41-50'
        when t3.customer_age between 51 and 60 then '51-60'
        else '61+' end as customer_age_group
from t1
join t2 on t1.product_id=t2.product_id
join t3 on t1.product_id=t3.product_id
where t3.rn=1
order by t1.total_sales desc,t1.product_id