Skip to main content

常见大数据面试SQL-奖金瓜分问题

一、题目​

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

样例数据

+----------+--------+
| 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

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

查询结果

+----------+--------+-----+
| 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。如果无限瓜分下去，看下是什么结果.

执行SQL

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

查询结果

+----------+--------+----------+
| user_id  | score  |  prize   |
+----------+--------+----------+
| 100      | 60     | 5000.0   |
| 101      | 45     | 2500.0   |
| 102      | 45     | 1250.0   |
| 103      | 35     | 625.0    |
| 104      | 30     | 312.5    |
| 105      | 25     | 156.25   |
| 106      | 15     | 78.125   |
| 107      | 10     | 39.0625  |
+----------+--------+----------+

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

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

执行SQL

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

执行结果

+----------+--------+---------+
| user_id  | score  |  prize  |
+----------+--------+---------+
| 100      | 60     | 5000.0  |
| 101      | 45     | 2500.0  |
| 102      | 45     | 1250.0  |
| 103      | 35     | 625.0   |
| 104      | 30     | 312.5   |
| 105      | 25     | 156.25  |
+----------+--------+---------+

四、建表语句和数据插入​

--建表语句
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',45),
('102',45),
('103',35),
('104',30),
('105',25),
('106',15),
('107',10)