1811. 寻找面试候选人

问题：面试候选人 的姓名 name 和邮件 mail。满足以下两个要求中的 任意一条 ，其成为 面试候选人 :
在连续三场及更多比赛中赢得任意奖牌。
三场及更多不同的比赛中赢得金牌

表: Contests

+--------------+------+
| Column Name  | Type |
+--------------+------+
| contest_id   | int  |
| gold_medal   | int  |
| silver_medal | int  |
| bronze_medal | int  |
+--------------+------+
1
2
3
4
5
6
7
8

contest_id 是该表的主键.
该表包含LeetCode竞赛的ID和该场比赛中金牌、银牌、铜牌的用户id。
可以保证，所有连续的比赛都有连续的ID，没有ID被跳过。

Table: Users

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| mail        | varchar |
| name        | varchar |
+-------------+---------+
1
2
3
4
5
6
7

user_id 是该表的主键.
该表包含用户信息。

查询结果格式如下例所示。

示例 1：

输入：
Contests表:

+------------+------------+--------------+--------------+
| contest_id | gold_medal | silver_medal | bronze_medal |
+------------+------------+--------------+--------------+
| 190        | 1          | 5            | 2            |
| 191        | 2          | 3            | 5            |
| 192        | 5          | 2            | 3            |
| 193        | 1          | 3            | 5            |
| 194        | 4          | 5            | 2            |
| 195        | 4          | 2            | 1            |
| 196        | 1          | 5            | 2            |
+------------+------------+--------------+--------------+
1
2
3
4
5
6
7
8
9
10
11

Users表:

+---------+--------------------+-------+
| user_id | mail               | name  |
+---------+--------------------+-------+
| 1       | sarah@leetcode.com | Sarah |
| 2       | bob@leetcode.com   | Bob   |
| 3       | alice@leetcode.com | Alice |
| 4       | hercy@leetcode.com | Hercy |
| 5       | quarz@leetcode.com | Quarz |
+---------+--------------------+-------+
1
2
3
4
5
6
7
8
9

输出：

+-------+--------------------+
| name  | mail               |
+-------+--------------------+
| Sarah | sarah@leetcode.com |
| Bob   | bob@leetcode.com   |
| Alice | alice@leetcode.com |
| Quarz | quarz@leetcode.com |
+-------+--------------------+
1
2
3
4
5
6
7
8

解释：
Sarah 赢得了3块金牌 (190, 193, and 196),所以我们将她列入结果表。
Bob在连续3场竞赛中赢得了奖牌(190, 191, and 192), 所以我们将他列入结果表。

• 注意他在另外的连续3场竞赛中也赢得了奖牌(194, 195, and 196).
  Alice在连续3场竞赛中赢得了奖牌 (191, 192, and 193), 所以我们将她列入结果表。
  Quarz在连续5场竞赛中赢得了奖牌(190, 191, 192, 193, and 194), 所以我们将他列入结果表。
  进阶：

如果第一个条件变成“该用户在连续 n 场及比赛中赢得任意奖牌。”呢？你如何更改你的解法，来选出面试候选人？可以把 n 想象成存储过程中的参数。
有的用户可能没有参加每一场竞赛，但是在参加的每一场竞赛中都表现得不错。你如何更改你的解法，以达到只考虑那些 用户参与了的 比赛？可假设另一张表给出了每场比赛的注册用户信息。

drop table if EXISTS Contests;
Create table If Not Exists Contests (contest_id int, gold_medal int, silver_medal int, bronze_medal int);
drop table if EXISTS Users;
Create table If Not Exists Users (user_id int, mail varchar(100), name varchar(100));
insert into Contests values (190,1,5,2);
insert into Contests values (191,2,3,5);
insert into Contests values (192,5,2,3);
insert into Contests values (193,1,3,5);
insert into Contests values (194,4,5,2);
insert into Contests values (195,4,2,1);
insert into Contests values (196,1,5,2);
select * from Contests;

insert into Users values (1,'sarah@leetcode.com','Sarah');
insert into Users values (2,'bob@leetcode.com','Bob');
insert into Users values (3,'alice@leetcode.com','Alice');
insert into Users values (4,'hercy@leetcode.com','Hercy');
insert into Users values (5,'quarz@leetcode.com','Quarz');
select * from Users;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

with t1 as (
    select contest_id,gold_medal id from Contests -- 所有 在比赛中 获奖的id
    union all
    select contest_id,silver_medal from Contests
    union all
    select contest_id,bronze_medal from Contests
),
t2 as (
-- 在三场及更多不同的比赛中赢得金牌
select distinct gold_medal id from Contests
group by gold_medal
having count(contest_id)>=3
) ,
t3 as (
-- 在连续三场及更多比赛中赢得任意奖牌
select distinct id from
(
select id,contest_id - row_number() over(partition by id order by contest_id) cr from t1
) s1
group by id,cr having count(1)>=3


union
-- 在三场及更多不同的比赛中赢得金牌 id
select distinct id from t2
)

select
    u.name,u.mail
from
    t3 left join Users u on t3.id = u.user_id
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31