SQL面试题练习 —— 奖金瓜分问题

1 题目

在活动大促中，有玩游戏瓜分奖金环节。现有奖金池为 10000元，代表奖金池中的初始额度。用户的分数信息如下表。表中的数据代表每一个用户和其对应的得分，user_id 和 score 都不会有重复值。瓜分奖金的规则如下：按照 score 从高到低依次瓜分，每个人都能分走当前奖金池里面剩余奖金的一半，当奖金池里面剩余的奖金少于 250 时（不含），则停止瓜分奖金。现在需要查询出所有分到奖金的 user_id 和其对应的奖金。

样例数据

2 建表语句

--建表语句
create table if not exists t15_user_score
(
    user_id string,
    score   bigint
);

--插入数据

insert into t15_user_score(user_id, score)
values
('100',60),
('101',50),
('102',45),
('103',35),
('104',30),
('105',25),
('106',15),
('107',10)
本文同步在

3 题解

1. 计算排名

select
    user_id,
    score,
    row_number() over(order by score desc) as rn
from t15_user_score;

执行结果

2. 瓜分奖金

每个人得到当前奖池的 $1/2$，排名 rn 的得到的为 $1/2^{rn}\ast 10000$，奖池剩余的也是$1/2^{rn}\ast 10000$。如果无限瓜分下去，看下是什么结果。

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

执行结果

3. 限制瓜分条件，得到最终结果

题目中要求：当奖金池里面剩余的奖金少于 250 时（不含），则停止瓜分奖金。我们可以看到104瓜分之后奖池剩余312.5应该继续瓜分，105瓜分完成之后才会停止。

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 - 1) * 10000 >=250

执行结果