Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1811.html
1811. Find Interview Candidates
Level
Medium
Description
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 | sarah@leetcode.com | Sarah |
| 2 | bob@leetcode.com | Bob |
| 3 | alice@leetcode.com | Alice |
| 4 | hercy@leetcode.com | Hercy |
| 5 | quarz@leetcode.com | Quarz |
+---------+--------------------+-------+
Result table:
+-------+--------------------+
| name | mail |
+-------+--------------------+
| Sarah | sarah@leetcode.com |
| Bob | bob@leetcode.com |
| Alice | alice@leetcode.com |
| Quarz | quarz@leetcode.com |
+-------+--------------------+
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.
Follow up:
- What if the first condition changed to be “any medal in
nor more consecutive contests”? How would you change your solution to get the interview candidates? Imagine that n is the parameter of a stored procedure. - Suppose we tracked which contests each user participated in. What if we wanted to consider candidates that won any medal in three or more consecutive contests where they were a participant? How would this change your solution?
Solution
For the first condition, select the users with user_id that occur in three consecutive entries of Contests table. For the second condition, select the users with user_id that occur in column gold_medal in at least three entries of Contests table.
# Write your MySQL query statement below
select u.name, u.mail from Users u where (
select count(c1.contest_id) from Contests c1, Contests c2, Contests c3
where c2.contest_id - c1.contest_id = 1 and c3.contest_id - c2.contest_id = 1
and (c1.gold_medal = u.user_id or c1.silver_medal = u.user_id or c1.bronze_medal = u.user_id)
and (c2.gold_medal = u.user_id or c2.silver_medal = u.user_id or c2.bronze_medal = u.user_id)
and (c3.gold_medal = u.user_id or c3.silver_medal = u.user_id or c3.bronze_medal = u.user_id)
) >= 1
or (
select count(*) from Contests c where c.gold_medal = u.user_id
) >= 3;