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

解题思路

难点在于统计单月最高销量（max_monthly_quantity）和购买量最多的客户年龄段（customer_age_group）字段。这两个字段需要单独开一个表来统计。因此，本人的解法是：

1. 表一统计product_id, total_sales, unit_price, total_quantity, avg_monthly_sales字段
2. 表二统计product_id, max_monthly_quantity字段
3. 表三统计product_id, customer_age_group字段
4. 总表通过表连接把表一、表二、表三合并在一起

# 表1 t 统计product_id, total_sales, unit_price, total_quantity, avg_monthly_sales字段
with t as (
    select
    p.product_id as product_id
    ,round(sum(o.quantity) * p.unit_price, 2) total_sales
    ,p.unit_price as unit_price
    ,round(sum(o.quantity), 2) total_quantity
    ,round(sum(o.quantity) * p.unit_price / 12, 2) avg_monthly_sales
    from orders as o
    inner join customers as c
    on o.customer_id = c.customer_id
    inner join products as p
    on o.product_id = p.product_id
    where year(o.order_date) = '2023'
    group by product_id, unit_price)
# 表2 t2 统计product_id, max_monthly_quantity字段
# 获取2023年最大的月总销量
, t2 as (
    select
    product_id
    ,round(max(monthly_quantity), 2) max_monthly_quantity
    from
  		# 子查询获取每个产品在2023年每个月的总销量
        (select
        product_id
        ,sum(quantity) monthly_quantity
        from orders
        where year(order_date) = '2023'
        group by product_id, month(order_date)
        ) as temp 
    group by product_id
# 表3 t3 统计product_id, customer_age_group字段
), t3 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
        (select
        o.product_id product_id
        ,c.customer_id customer_id
        ,c.customer_age customer_age
		 # 窗口函数，每个产品按照每个客户的总购买量来排序
        ,rank()over(partition by o.product_id order by sum(o.quantity) desc, c.customer_age) rk
        from orders as o
        inner join customers as c
        on o.customer_id = c.customer_id
        where year(o.order_date) = '2023'
        group by product_id, customer_id, customer_age) as temp2
    where rk = 1
)
# 合并表1 t，表2 t2，表3 t3各自统计的字段
select 
t.product_id product_id
,total_sales
,unit_price
,total_quantity
,avg_monthly_sales
,max_monthly_quantity
,customer_age_group
from t
inner join t2
on t.product_id = t2.product_id
inner join t3
on t.product_id = t3.product_id
order by total_sales desc, product_id