Find Interview Candidates
题目分析:题目的问题是
翻译过来的意思就是:找出连续三场赢得奖牌的人或者赢得了三个金牌的人。给了contests和users两张表。
首先第一个,找出赢得了三个金牌的人很简单,对gold_medel那一行进行group by即可
select gold_medal from Contests group by gold_medal having count(*) >= 3;
接着第二点,连续三场获得奖牌,这个就有点意思,首先contests表给的是金牌银牌铜牌三个都有的,所以需要列转行,使用union all。思路与这题完全相同leetcode1783 列转行
select contest_id,gold_medal id from Contests union all
select contest_id,silver_medal from Contests union all
select contest_id,bronze_medal from Contests order by id,contest_id
接着采用row_number()进行编号相减,思路与这一题完全相同 leetcode 1258 窗口函数的妙用
select distinct id from (
select contest_id,contest_id-rr cr,id from (
select contest_id,row_number() over(partition by id order by contest_id) rr,id from (
select contest_id,gold_medal id from Contests union all
select contest_id,silver_medal from Contests union all
select contest_id,bronze_medal from Contests order by id,contest_id) F1) F2) F3 group by id,cr having count(*) >= 3
对上面的结果进行inner join得到最终结果
select U.name,U.mail from Users U inner join (select gold_medal id from Contests group by gold_medal having count(*) >= 3 union
select distinct id from (
select contest_id,contest_id-rr cr,id from (
select contest_id,row_number() over(partition by id order by contest_id) rr,id from (
select contest_id,gold_medal id from Contests union all
select contest_id,silver_medal from Contests union all
select contest_id,bronze_medal from Contests order by id,contest_id) F1) F2) F3 group by id,cr having count(*) >= 3) F on U.user_id = F.id
提交结果如下: