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

数据仓库晨曦

发布于 2024-07-26 14:16:18

310

发布于 2024-07-26 14:16:18

文章被收录于专栏：数据仓库技术

一、题目

在活动大促中，有玩游戏瓜分奖金环节。现有奖金池为 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。所以限制1/2^rn*10000>=250的行即可。

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

查询结果

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

四、建表语句和数据插入

--建表语句
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)

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2024-07-26，如有侵权请联系 cloudcommunity@tencent.com 删除

row