1811.(Medium)Find Interview Candidates

Table: Contests

+--------------+------+
| Column Name  | Type |
+--------------+------+
| contest_id   | int  |
| gold_medal   | int  |
| silver_medal | int  |
| bronze_medal | int  |
+--------------+------+
contest_id is the primary key for this table.
This table contains the LeetCode contest ID and the user IDs of the gold, silver, and bronze medalists.
It is guaranteed that any consecutive contests have consecutive IDs and that no ID is skipped.

Table: Users

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| mail        | varchar |
| name        | varchar |
+-------------+---------+
user_id is the primary key for this table.
This table contains information about the users.

Write an SQL query to report the name and the mail of all interview candidates. A user is an interview candidate if at least one of these two conditions is true:

The user won any medal in three or more consecutive contests. The user won the gold medal in three or more different contests (not necessarily consecutive). Return the result table in any order.

The query result format is in the following example:

Contests table:
+------------+------------+--------------+--------------+
| 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            |
+------------+------------+--------------+--------------+

Users table:
+---------+--------------------+-------+
| user_id | mail               | name  |
+---------+--------------------+-------+
| 1       | [e-mail] | Sarah |
| 2       | [e-mail]   | Bob   |
| 3       | [e-mail] | Alice |
| 4       | [e-mail] | Hercy |
| 5       | [e-mail] | Quarz |
+---------+--------------------+-------+

Result table:
+-------+--------------------+
| name  | mail               |
+-------+--------------------+
| Sarah | [e-mail] |
| Bob   | [e-mail]   |
| Alice | [e-mail] |
| Quarz | [e-mail] |
+-------+--------------------+

Sarah won 3 gold medals (190, 193, and 196), so we include her in the result table.
Bob won a medal in 3 consecutive contests (190, 191, and 192), so we include him in the result table.
    - Note that he also won a medal in 3 other consecutive contests (194, 195, and 196).
Alice won a medal in 3 consecutive contests (191, 192, and 193), so we include her in the result table.
Quarz won a medal in 5 consecutive contests (190, 191, 192, 193, and 194), so we include them in the result table.

来源：力扣（LeetCode）

链接：https://leetcode-cn.com/problems/find-interview-candidates

著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

Solution

1. t表列出所有选手的获奖情况，窗口函数做出连续获奖reference column

2. u表对两个条件筛出的user_id取并集：金牌3次 union 连续获奖3次

with t as 
(
    SELECT *,  
        contest_id - RANK() over (PARTITION BY user_id,user_id in (gold_medal, silver_medal, bronze_medal)  order by contest_id) consecutive_ref
    FROM Contests JOIN Users on user_id in (gold_medal, silver_medal, bronze_medal)
    order by user_id, contest_id
),
u as 
(
    SELECT user_id 
    from t 
    GROUP BY 1
    having sum(user_id=gold_medal)>=3
        union 
    select user_id
    FROM t 
    GROUP BY user_id,consecutive_ref
    HAVING count(1) >=3
)
SELECT name, mail FROM u LEFT JOIN Users using(user_id);

Table Schema

Create table If Not Exists Contests (contest_id int, gold_medal int, silver_medal int, bronze_medal int);
Create table If Not Exists Users (user_id int, mail varchar(50), name varchar(30));
Truncate table Contests;
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('190', '1', '5', '2');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('191', '2', '3', '5');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('192', '5', '2', '3');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('193', '1', '3', '5');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('194', '4', '5', '2');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('195', '4', '2', '1');
insert into Contests (contest_id, gold_medal, silver_medal, bronze_medal) values ('196', '1', '5', '2');
Truncate table Users;
insert into Users (user_id, mail, name) values ('1', '[e-mail]', 'Sarah');
insert into Users (user_id, mail, name) values ('2', '[e-mail]', 'Bob');
insert into Users (user_id, mail, name) values ('3', '[e-mail]', 'Alice');
insert into Users (user_id, mail, name) values ('4', '[e-mail]', 'Hercy');
insert into Users (user_id, mail, name) values ('5', '[e-mail]', 'Quarz');