0% completed
Problem
Table: Contests
+--------------+------+
| Column Name | Type |
+--------------+------+
| contest_id | int |
| gold_medal | int |
| silver_medal | int |
| bronze_medal | int |
+--------------+------+
contest_id is the column with unique values for this table.
This table contains the Design Guru 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 column with unique values for this table.
This table contains information about the users.
Problem Definition
Write a solution 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.
Example
Output
Try It Yourself
Solution
To identify the interview candidates based on their performance in contests, we need to evaluate each user's achievements according to the specified criteria:
- Winning any medal in three or more consecutive contests.
- Winning the gold medal in three or more different contests (not necessarily consecutive).
The solution involves processing the Contests
and Users
tables to determine which users meet at least one of these conditions. We'll achieve this by performing a series of steps using Common Table Expressions (CTEs) and SQL window functions.
Approach Overview
- Gather All Medalists: Combine the gold, silver, and bronze medalists from all contests into a single unified list.
- Assign Row Numbers to Each User's Contests: For each user, order their contest participations by
contest_id
and assign a sequential row number. This helps in identifying consecutive contests. - Identify Users with Medals in Three or More Consecutive Contests: Utilize the difference between
contest_id
and the row number to group consecutive contests. Users with a group size of three or more meet the first condition. - Identify Users with Gold Medals in Three or More Contests: Count the number of times each user has won the gold medal across all contests. Users with three or more gold medals meet the second condition.
- Combine Both Sets of Users: Union the users identified in the previous two steps to obtain all interview candidates.6. Retrieve Candidate Details: Join the combined list of candidates with the
Users
table to fetch theirname
andmail
. - Finalize the Results: Select distinct candidate details and present them in the desired format.
SQL Query
WITH t0 AS ( SELECT gold_medal AS USER, contest_id FROM Contests UNION ALL SELECT silver_medal AS USER, contest_id FROM Contests UNION ALL SELECT bronze_medal AS USER, contest_id FROM Contests ), t1 AS ( SELECT USER, contest_id, ROW_NUMBER() OVER (PARTITION BY USER ORDER BY contest_id) AS rn FROM t0 ), t2 AS ( -- Users who won any medal in three or more consecutive contests SELECT USER AS user_id FROM t1 GROUP BY USER, contest_id - rn HAVING COUNT(*) >= 3 UNION ALL -- Users who won the gold medal in three or more contests SELECT gold_medal AS user_id FROM Contests GROUP BY gold_medal HAVING COUNT(*) >= 3 ) SELECT DISTINCT u.name AS NAME, u.mail AS mail FROM t2 JOIN Users u ON t2.user_id = u.user_id;
Step-by-Step Approach
Step 1: Gather All Medalists (t0
)
Create a unified list of all users who have won any medal (gold, silver, or bronze) across all contests.
SQL Query:
WITH t0 AS ( SELECT gold_medal AS USER, contest_id FROM Contests UNION ALL SELECT silver_medal AS USER, contest_id FROM Contests UNION ALL SELECT bronze_medal AS USER, contest_id FROM Contests ) SELECT * FROM t0;
Explanation:
UNION ALL
:- Combines the results of selecting gold, silver, and bronze medalists into a single table without removing duplicates.
AS USER
:- Aliases the medalist columns (
gold_medal
,silver_medal
,bronze_medal
) asUSER
for uniformity.
- Aliases the medalist columns (
Output After Step 1:
+------+------------+ | USER | contest_id | +------+------------+ | 1 | 190 | | 5 | 190 | | 2 | 190 | | 2 | 191 | | 3 | 191 | | 5 | 191 | | 5 | 192 | | 2 | 192 | | 3 | 192 | | 1 | 193 | | 3 | 193 | | 5 | 193 | | 4 | 194 | | 5 | 194 | | 2 | 194 | | 4 | 195 | | 2 | 195 | | 1 | 195 | | 1 | 196 | | 5 | 196 | | 2 | 196 | +------+------------+
Step 2: Assign Row Numbers to Each User's Contests (t1
)
For each user, assign a sequential row number based on the order of contest_id
. This helps in identifying consecutive contests.
SQL Query:
WITH t0 AS ( SELECT gold_medal AS USER, contest_id FROM Contests UNION ALL SELECT silver_medal AS USER, contest_id FROM Contests UNION ALL SELECT bronze_medal AS USER, contest_id FROM Contests ), t1 AS ( SELECT USER, contest_id, ROW_NUMBER() OVER (PARTITION BY USER ORDER BY contest_id) AS rn FROM t0 ) SELECT * FROM t1;
Explanation:
ROW_NUMBER() OVER (PARTITION BY USER ORDER BY contest_id) AS rn
:- Assigns a unique sequential number to each contest a user has participated in, ordered by
contest_id
.
- Assigns a unique sequential number to each contest a user has participated in, ordered by
PARTITION BY USER
:- Ensures that the row numbering restarts for each user.
Output After Step 2:
+------+------------+----+ | USER | contest_id | rn | +------+------------+----+ | 1 | 190 | 1 | | 1 | 193 | 2 | | 1 | 195 | 3 | | 1 | 196 | 4 | | 2 | 190 | 1 | | 2 | 191 | 2 | | 2 | 192 | 3 | | 2 | 194 | 4 | | 2 | 195 | 5 | | 2 | 196 | 6 | | 3 | 191 | 1 | | 3 | 192 | 2 | | 3 | 193 | 3 | | 4 | 194 | 1 | | 4 | 195 | 2 | | 5 | 190 | 1 | | 5 | 191 | 2 | | 5 | 192 | 3 | | 5 | 193 | 4 | | 5 | 194 | 5 | | 5 | 196 | 6 | +------+------------+----+
Step 3: Identify Users with Medals in Three or More Consecutive Contests (t2
)
Determine which users have won any medal in three or more consecutive contests or have won the gold medal in three or more different contests.
SQL Query:
WITH t0 AS ( SELECT gold_medal AS USER, contest_id FROM Contests UNION ALL SELECT silver_medal AS USER, contest_id FROM Contests UNION ALL SELECT bronze_medal AS USER, contest_id FROM Contests ), t1 AS ( SELECT USER, contest_id, ROW_NUMBER() OVER (PARTITION BY USER ORDER BY contest_id) AS rn FROM t0 ), t2 AS ( -- Users who won any medal in three or more consecutive contests SELECT USER AS user_id FROM t1 GROUP BY USER, contest_id - rn HAVING COUNT(*) >= 3 UNION ALL -- Users who won the gold medal in three or more contests SELECT gold_medal AS user_id FROM Contests GROUP BY gold_medal HAVING COUNT(*) >= 3 ) SELECT * FROM t2;
Explanation:
- First Part of
t2
:GROUP BY USER, contest_id - rn
:- Groups the contests for each user by the difference between
contest_id
and their row number. This technique identifies sequences of consecutive contests.
- Groups the contests for each user by the difference between
HAVING COUNT(*) >= 3
:- Filters groups where the user has participated in three or more consecutive contests.
- Second Part of
t2
:SELECT gold_medal AS user_id FROM Contests
:- Selects users who have won the gold medal.
GROUP BY gold_medal HAVING COUNT(*) >= 3
:- Identifies users who have won the gold medal in three or more different contests.
Output After Step 3:
+---------+ | user_id | +---------+ | 1 | | 2 | | 3 | | 5 | +---------+
Step 4: Retrieve Candidate Details
Fetch the name
and mail
of the users identified as interview candidates in t2
.
SQL Query:
WITH t0 AS ( SELECT gold_medal AS USER, contest_id FROM Contests UNION ALL SELECT silver_medal AS USER, contest_id FROM Contests UNION ALL SELECT bronze_medal AS USER, contest_id FROM Contests ), t1 AS ( SELECT USER, contest_id, ROW_NUMBER() OVER (PARTITION BY USER ORDER BY contest_id) AS rn FROM t0 ), t2 AS ( -- Users who won any medal in three or more consecutive contests SELECT USER AS user_id FROM t1 GROUP BY USER, contest_id - rn HAVING COUNT(*) >= 3 UNION ALL -- Users who won the gold medal in three or more contests SELECT gold_medal AS user_id FROM Contests GROUP BY gold_medal HAVING COUNT(*) >= 3 ) SELECT DISTINCT u.name AS NAME, u.mail AS mail FROM t2 JOIN Users u ON t2.user_id = u.user_id;
Explanation:
JOIN Users u ON t2.user_id = u.user_id
:- Links the list of interview candidates (
t2
) with their corresponding details in theUsers
table.
- Links the list of interview candidates (
SELECT DISTINCT
:- Ensures that each candidate appears only once in the final output, even if they meet both criteria.
Output After Step 4:
+-------+----------------------+ | NAME | mail | +-------+----------------------+ | Sarah | sarah@designguru.com | | Bob | bob@designguru.com | | Alice | alice@designguru.com | | Quarz | quarz@designguru.com | +-------+----------------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible