SQL面试题挑战03:奖金瓜分问题
问题
在活动大促中,有玩游戏瓜分奖金环节。现有奖金池为3000元,代表奖金池中的初始额度。用户的分数信息如下:
user_id score
101 45
102 40
103 35
104 30
105 25
表中的数据代表每一个用户和其对应的得分,user_id和score都不会有重复值。瓜分奖金的规则如下:按照score从高到低依次瓜分,每个人都能分走当前奖金池里面剩余奖金的一半,当奖金池里面剩余的奖金少于500时(不含),则停止瓜分奖金。
现在需要查询出所有分到奖金的user_id和其对应的奖金。
建表语句
drop table if exists scores;
create table scores(
user_id varchar(20),
score float
);
insert into scores
select 101 as user_id,45 as score
union all
select 102 as user_id,40 as score
union all
select 103 as user_id,35 as score
union all
select 104 as user_id,30 as score
union all
select 105 as user_id,25 as score
解答
思路:
第1名可以得到总奖金的 1 2 \frac{1}{2} 21,第2名可以得到总奖金的 1 4 \frac{1}{4} 41,第 i i i名可以得到总奖金的 1 2 i \frac{1}{2^i} 2i1。 因此对得分求排名即可,但注意需要注意当剩余奖金少于500时停止瓜分,也就是当某个人的所得奖金小于250时就停止瓜分。
select
user_id,
if(bonus<250,0,bonus)bonus
from
(
select
user_id,
pow(0.5,row_number() over (order by score desc))*3000 bonus
from
scores
)t1
注意:这里没有考虑score相同的情况,题目给定的条件也有限,因此这里没有做其他处理。