Home › Databases › SQL Practice Problems

Find Interview Candidates

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

java

-- TODO: Write your user queries here

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 their name and mail.
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 |
+-------+----------------------+

🤖 Don't fully get this? Learn it with Claude

Stuck on Find Interview Candidates? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.

🎨 Explain it visually

Build the mental picture, not memorization.

I just read a lesson on **Find Interview Candidates** (Databases) and want to truly understand it. Explain Find Interview Candidates from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🤔 Walk me through it (interactive)

Socratic — adapts to where you're stuck.

Teach me **Find Interview Candidates** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧪 Quiz me & fix my gaps

Active recall exposes what you missed.

Quiz me on **Find Interview Candidates** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

🧠 Make it stick

Intuition + hook + flashcards for long-term memory.

Help me remember **Find Interview Candidates** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

📝 My notes

← Active Users Suspicious Bank Accounts →

Find Interview Candidates

Problem

Problem Definition

Example

Output

Try It Yourself

Solution

Approach Overview

SQL Query

Step-by-Step Approach

Step 1: Gather All Medalists (t0)

Step 2: Assign Row Numbers to Each User's Contests (t1)

Step 3: Identify Users with Medals in Three or More Consecutive Contests (t2)

Step 4: Retrieve Candidate Details

📝 My notes

Step 1: Gather All Medalists (`t0`)

Step 2: Assign Row Numbers to Each User's Contests (`t1`)

Step 3: Identify Users with Medals in Three or More Consecutive Contests (`t2`)