leetcode 1811. Find Interview Candidates---窗口函数相减

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

提交结果如下：