一、题目
在活动大促中，有玩游戏瓜分奖金环节。现有奖金池为 10000元，代表奖金池中的初始额度。用户的分数信息如下表。表中的数据代表每一个用户和其对应的得分，user_id 和 score 都不会有重复值。瓜分奖金的规则如下：按照 score 从高到低依次瓜分，每个人都能分走当前奖金池里面剩余奖金的一半，当奖金池里面剩余的奖金少于 250 时（不含），则停止瓜分奖金。现在需要查询出所有分到奖金的 user_id 和其对应的奖金。
样例数据
代码语言：javascript复制 ---------- --------
| user_id | score |
---------- --------
| 100 | 60 |
| 101 | 45 |
| 102 | 45 |
| 103 | 35 |
| 104 | 30 |
| 105 | 25 |
| 106 | 15 |
| 107 | 10 |
---------- --------
二、分析
该题目相对简单，给用户根据score进行排名，然后依次给出金额即可。难点是如何计算瓜分金额、如何计算奖池余额。
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️ |
三、SQL
1.计算排名
使用row_number开窗，得到用户排名rn
执行SQL
代码语言：javascript复制select
user_id,
score,
row_number() over(order by score desc) as rn
from t15_user_score;
查询结果
代码语言：javascript复制 ---------- -------- -----
| user_id | score | rn |
---------- -------- -----
| 100 | 60 | 1 |
| 102 | 45 | 2 |
| 101 | 45 | 3 |
| 103 | 35 | 4 |
| 104 | 30 | 5 |
| 105 | 25 | 6 |
| 106 | 15 | 7 |
| 107 | 10 | 8 |
---------- -------- -----
2.瓜分奖金
每个人得到当前奖池的1/2,排名rn的得到的为1/2^rn10000，奖池剩余的也是1/2^rn10000。所以限制1/2^rn*10000>=250的行即可。
执行SQL
代码语言：javascript复制select user_id,
score,
power(0.5, rn) * 10000 as prize
from (select user_id,
score,
row_number() over (order by score desc) as rn
from t15_user_score) t
where power(0.5, rn) * 10000 >=250
查询结果
代码语言：javascript复制 ---------- -------- ---------
| user_id | score | prize |
---------- -------- ---------
| 100 | 60 | 5000.0 |
| 102 | 45 | 2500.0 |
| 101 | 45 | 1250.0 |
| 103 | 35 | 625.0 |
| 104 | 30 | 312.5 |
---------- -------- ---------
四、建表语句和数据插入
代码语言：javascript复制--建表语句
create table if not exists t15_user_score
(
user_id string,
score bigint
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS orc;
--插入数据
insert into t15_user_score(user_id, score)
values
('100',60),
('101',45),
('102',45),
('103',35),
('104',30),
('105',25),
('106',15),
('107',10)
