题目需求
从订单信息表 (order_info
) 中统计每个用户截止到某个下单日期的累积消费金额,以及每个用户在其每个下单日期的 VIP 等级。计算规则如下:
- 当累积消费金额为
X
时:
0<=X < 10000
,则为普通会员10000 <= X < 30000
,则为青铜会员30000 <= X < 50000
,则为白银会员50000 <= X < 80000
,则为黄金会员80000 <= X < 100000
,则为铂金会员X >= 100000
,则为钻石会员
期望结果如下:
user_id | create_date | sum_so_far | vip_level |
---|
101 | 2024-09-27 | 99500.00 | 铂金会员 |
101 | 2024-09-28 | 99500.00 | 铂金会员 |
101 | 2024-09-29 | 142800.00 | 钻石会员 |
101 | 2024-09-30 | 143660.00 | 钻石会员 |
102 | 2024-10-01 | 171680.00 | 钻石会员 |
103 | 2024-10-02 | 17850.00 | 青铜会员 |
104 | 2024-10-03 | 69980.00 | 黄金会员 |
105 | 2024-10-04 | 120100.00 | 钻石会员 |
106 | 2024-10-04 | 9390.00 | 普通会员 |
106 | 2024-10-05 | 119150.00 | 钻石会员 |
107 | 2024-10-06 | 69850.00 | 黄金会员 |
108 | 2024-10-07 | 155770.00 | 钻石会员 |
109 | 2024-10-08 | 24020.00 | 青铜会员 |
110 | 2024-10-09 | 153500.00 | 钻石会员 |
需要用到的表:
订单信息表:order_info
order_id | user_id | create_date | total_amount |
---|
1 | 101 | 2024-09-30 | 29000.00 |
10 | 103 | 2024-10-02 | 28000.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 '白金会员'
when sum_so_far >= 100000 then '钻石会员'
else '其他'
end as vip_level
from
(
select
user_id,
create_date,
sum(total) over (partition by user_id order by create_date ) sum_so_far
from
(
select
user_id,
create_date,
sum(total_amount) total
from
order_info
group by
user_id,
create_date
) t
) tt