Find Interview Candidates - Practice Coding Problems

Find Interview Candidates - Problem

Database Medium

You are given two tables: Contests and Users.

The Contests table contains information about LeetCode contests and their medalists:

contest_id: Unique identifier for each contest
gold_medal: User ID of the gold medalist
silver_medal: User ID of the silver medalist
bronze_medal: User ID of the bronze medalist

The Users table contains user information:

user_id: Unique identifier for each user
mail: User's email address
name: User's name

Task: Find all interview candidates. A user qualifies as an interview candidate if:

They won any medal in three or more consecutive contests, OR
They won the gold medal in three or more different contests (not necessarily consecutive)

Return the name and mail of all interview candidates in any order.

Table Schema

Contests

Column Name	Type	Description
`contest_id` PK	int	Unique contest identifier
`gold_medal`	int	User ID of gold medalist
`silver_medal`	int	User ID of silver medalist
`bronze_medal`	int	User ID of bronze medalist

Primary Key: contest_id

Users

Column Name	Type	Description
`user_id` PK	int	Unique user identifier
`mail`	varchar	User's email address
`name`	varchar	User's name

Primary Key: user_id

Input & Output

Example 1 — Consecutive Medals Winner

Input Tables:

Contests

contest_id	gold_medal	silver_medal	bronze_medal
190	1	2	3
191	1	4	5
192	1	6	7
193	8	9	10

Users

user_id	mail	name
1	[email protected]	Alice
2	[email protected]	Bob
8	[email protected]	Charlie

Output:

name	mail
Alice	[email protected]

💡 Note:

Alice (user_id=1) won gold medals in 3 consecutive contests (190, 191, 192), qualifying her as an interview candidate. She also meets the consecutive medal requirement by winning any medal in 3+ consecutive contests.

Example 2 — Multiple Gold Winner

Input Tables:

Contests

contest_id	gold_medal	silver_medal	bronze_medal
190	1	2	3
192	1	4	5
195	1	6	7
200	8	9	10

Users

user_id	mail	name
1	[email protected]	Alice
8	[email protected]	Charlie

Output:

name	mail
Alice	[email protected]

💡 Note:

Alice (user_id=1) won gold medals in 3 different contests (190, 192, 195), qualifying her as an interview candidate based on the gold medal criterion. The contests are not consecutive, but that's not required for gold medal qualification.

Example 3 — No Qualified Candidates

Input Tables:

Contests

contest_id	gold_medal	silver_medal	bronze_medal
190	1	2	3
195	4	5	6

Users

user_id	mail	name
1	[email protected]	Alice
2	[email protected]	Bob

Output:

name	mail

💡 Note:

No user qualifies as an interview candidate. User 1 won only 1 gold medal (need 3+), and no user won medals in 3+ consecutive contests (only 2 contests total).

Constraints

1 ≤ contest_id ≤ 1000
1 ≤ gold_medal, silver_medal, bronze_medal ≤ 10^6
1 ≤ user_id ≤ 10^6
All contest_id values are unique
Consecutive contests have consecutive IDs with no gaps

Visualization

Tap to expand

Understanding the Visualization

Input

Contests and Users tables

Analysis

Check consecutive medals and gold medal count

Output

Qualified candidates

Key Takeaway

🎯 Key Insight: Use UNION to combine multiple qualification criteria efficiently

Asked in

M Meta 12 G Google 8 A Amazon 6

Use CTE with UNION to handle both qualification criteria separately: flatten medal data with UNION ALL, use ROW_NUMBER() window function to detect consecutive contest participation, and simple GROUP BY for gold medal counting.

Table Schema

Contests

Column Name	Type	Description
`contest_id` PK	int	Unique contest identifier
`gold_medal`	int	User ID of gold medalist
`silver_medal`	int	User ID of silver medalist
`bronze_medal`	int	User ID of bronze medalist

Primary Key: contest_id

Users

Column Name	Type	Description
`user_id` PK	int	Unique user identifier
`mail`	varchar	User's email address
`name`	varchar	User's name

Primary Key: user_id

Common Approaches

Approach	Time	Space	Notes
✓ CTE with UNION	O(n log n)	O(n)	Use CTEs to handle both qualification criteria separately, then UNION results

CTE with UNION — Algorithm Steps

Step 1: Create CTE to flatten medal data (UNPIVOT medals)
Step 2: Find users with 3+ consecutive medal contests using window functions
Step 3: Find users with 3+ gold medals using GROUP BY
Step 4: UNION both result sets and JOIN with Users table

Visualization

Tap to expand

Step-by-Step Walkthrough

Flatten

UNPIVOT medal columns

Analyze

Check consecutive and gold criteria

Union

Combine qualified users

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Window function ordering and grouping operations

⚡ Linearithmic

Space Complexity

O(n)

CTE storage and intermediate results

⚡ Linearithmic Space

28.5K Views

Medium Frequency

~18 min Avg. Time

892 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

CTE with UNION — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler