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

# 1、计算产品下每个用户的购买量，并按年龄段分组加和
with t1 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
        ,sum(c_total_quantity) as c_gp_total_quatity
    from (
        select
            product_id
            ,customer_id
            ,sum(quantity) as c_total_quantity
        from orders
        where year(order_date) = 2023
        group by 1,2
    ) t 
    left join customers c on t.customer_id = c.customer_id
    group by 1,2
),
# 2、计算年龄段的购买量排序
t as (
    select
    product_id
    ,customer_age_group
    ,row_number() over(partition by product_id order by c_gp_total_quatity desc, customer_age_group asc) as rk
    from t1
),

# 3、将产品按月份进行分组，计算月销售额和销售量
t2 as (
    select
    o.product_id
    ,p.unit_price
    ,month(order_date) as mon
    ,sum(quantity) as mon_total_quantity
    ,sum(p.unit_price*quantity) as mon_total_sales
    # ,rank() over(partition by product_id, month(order_date) order by sum(quantity) desc) as q_rk
    from orders o
    left join products p
    on p.product_id = o.product_id
    where year(order_date) = 2023
    group by 1,2,3
)

select
t2.product_id
,round(sum(mon_total_sales), 2) as total_sales
,t2.unit_price
,round(sum(mon_total_quantity), 2) as total_quantity
,round(sum(mon_total_sales)/12, 2) as avg_monthly_sales
,max(mon_total_quantity) as max_monthly_quantity
,t.customer_age_group
from t2
left join t on t.product_id = t2.product_id and t.rk = 1
group by t2.product_id, t2.unit_price, t.customer_age_group
order by total_sales desc, t2.product_id asc
;