Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
14. Find Interview Candidates
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

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

Image

Output

Image

Try It Yourself

MYSQL
MYSQL
. . . .

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:

  1. Winning any medal in three or more consecutive contests.
  2. 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

  1. Gather All Medalists: Combine the gold, silver, and bronze medalists from all contests into a single unified list.
  2. 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.
  3. 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.
  4. 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.
  5. 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 their name and mail.
  6. 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) as USER for uniformity.

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.
  • 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.
    • 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 the Users table.
  • 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 |
+-------+----------------------+

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible