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

关键思路：使用CTE明晰逻辑，分别计算商品销量和客户信息，最后表连接。计算逻辑简单，主要是各字段的计算和分组。

with product_quantity as(
    select product_id
    ,max(num) max_monthly_quantity
    ,sum(num) total_quantity
    from (
        select product_id
        ,month(order_date) m
        ,sum(quantity) num
        from orders
        where order_date between '2023-01-01' and '2023-12-31'
        group by product_id,month(order_date)
    ) t1
    group by product_id
),

customers_info as (
    select product_id
    ,customer_id
    ,customer_age_group
    from(
    select product_id
    ,customer_id
    ,rank()over(partition by product_id order by total_quan_cu desc,customer_age) rk
    ,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'
        when customer_age > 61 then '61+'
    end customer_age_group
    from (
        select product_id
        ,o.customer_id
        ,sum(quantity) total_quan_cu
        ,customer_age
        from orders o 
        left join customers c on o.customer_id=c.customer_id
        where order_date between '2023-01-01' and '2023-12-31'
        group by product_id,o.customer_id,customer_age
    ) t2
    )t3
    where rk=1
)

select p.product_id product_id
,round(total_quantity*unit_price*1.0,2) total_sales
,round(unit_price,2) unit_price
,total_quantity
,round(total_quantity*unit_price/12,2) avg_monthly_sales
,max_monthly_quantity
,customer_age_group
from product_quantity p 
left join customers_info c on p.product_id=c.product_id
left join products pd on p.product_id=pd.product_id
order by total_sales desc,product_id