查询用户的累计消费金额及VIP等级
题目需求
从订单信息表(order_info)中统计每个用户截止其每个下单日期的累积消费金额,以及每个用户在其每个下单日期的VIP等级。
用户vip等级根据累积消费金额计算,计算规则如下:
设累积消费总额为X,
若0=<X<10000,则vip等级为普通会员
若10000<=X<30000,则vip等级为青铜会员
若30000<=X<50000,则vip等级为白银会员
若50000<=X<80000,则vip为黄金会员
若80000<=X<100000,则vip等级为白金会员
若X>=100000,则vip等级为钻石会员
期望结果如下:
user_id(用户id) | create_date(下单日期) | sum_so_far(截至每个下单日期的累计下单金额) | vip_level(每个下单日期的VIP等级) |
---|---|---|---|
101 | 2021-09-27 | 29000.00 | 青铜会员 |
101 | 2021-09-28 | 99500.00 | 白金会员 |
101 | 2021-09-29 | 142800.00 | 钻石会员 |
101 | 2021-09-30 | 143660.00 | 钻石会员 |
102 | 2021-10-01 | 171680.00 | 钻石会员 |
102 | 2021-10-02 | 177850.00 | 钻石会员 |
103 | 2021-10-02 | 69980.00 | 黄金会员 |
103 | 2021-10-03 | 75890.00 | 黄金会员 |
104 | 2021-10-03 | 89880.00 | 白金会员 |
105 | 2021-10-04 | 120100.00 | 钻石会员 |
106 | 2021-10-04 | 9390.00 | 普通会员 |
106 | 2021-10-05 | 119150.00 | 钻石会员 |
107 | 2021-10-05 | 69850.00 | 黄金会员 |
107 | 2021-10-06 | 124150.00 | 钻石会员 |
108 | 2021-10-06 | 101070.00 | 钻石会员 |
108 | 2021-10-07 | 155770.00 | 钻石会员 |
109 | 2021-10-07 | 129480.00 | 钻石会员 |
109 | 2021-10-08 | 153500.00 | 钻石会员 |
1010 | 2021-10-08 | 51950.00 | 黄金会员 |
代码实现
select
user_id,
create_date,
sum_so_far,
case
when sum_so_far>=0 and sum_so_far<10000 then '普通会员'
when sum_so_far>=10000 and sum_so_far<30000 then '青铜会员'
when sum_so_far>=30000 and sum_so_far<50000 then '白银会员'
when sum_so_far>=50000 and sum_so_far<80000 then '黄金会员'
when sum_so_far>=80000 and sum_so_far<100000 then '白金会员'
ELSE '钻石会员'
end `会员等级`
from
(
SELECT
user_id,
create_date,
sum(total_amount_per_day) over (partition by user_id order by create_date) sum_so_far
FROM
(
select
user_id,
create_date,
sum(total_amount) total_amount_per_day
from order_info
group by user_id,create_date
)t1
)t2