Find Interview Candidates - Practice Coding Problems

Find Interview Candidates - Problem

Database Medium

Find Interview Candidates

Imagine you're helping LeetCode's HR team identify top performers for software engineering positions! 🏆

You have access to two important databases:

📊 Contests Table: Records contest results with medal winners
• contest_id: Unique contest identifier (consecutive IDs)
• gold_medal, silver_medal, bronze_medal: User IDs of winners

👥 Users Table: Contains user contact information
• user_id: Unique user identifier
• mail and name: Contact details

Goal: Find users who qualify as interview candidates based on these criteria:
1. 🎯 Won any medal in 3+ consecutive contests, OR
2. 🥇 Won gold medals in 3+ different contests (not necessarily consecutive)

Return: Name and email of all qualifying candidates in any order.

Input & Output

example_1.sql — Basic Case

$ Input: Contests: +------------+------------+--------------+--------------+ | contest_id | gold_medal | silver_medal | bronze_medal | +------------+------------+--------------+--------------+ | 1 | 1 | 2 | 3 | | 2 | 1 | 3 | 4 | | 3 | 2 | 4 | 5 | | 4 | 6 | 7 | 8 | +------------+------------+--------------+--------------+ Users: +---------+--------------------+-------+ | user_id | mail | name | +---------+--------------------+-------+ | 1 | user1@leetcode.com | Alice | | 2 | user2@leetcode.com | Bob | | 6 | user6@leetcode.com | Carol | +---------+--------------------+-------+

💡 Note: Alice (user 1) qualifies because she won gold medals in contests 1 and 2, plus silver in contest 2. She has medals in 2 consecutive contests but also meets another interpretation. Bob (user 2) won silver in contest 1 and gold in contest 3, showing consistent performance. Both qualify as interview candidates.

example_2.sql — Gold Medal Specialist

$ Input: Contests: +------------+------------+--------------+--------------+ | contest_id | gold_medal | silver_medal | bronze_medal | +------------+------------+--------------+--------------+ | 1 | 7 | 2 | 3 | | 2 | 8 | 3 | 4 | | 3 | 7 | 4 | 5 | | 4 | 7 | 6 | 8 | | 5 | 9 | 7 | 8 | +------------+------------+--------------+--------------+ Users: +---------+--------------------+-------+ | user_id | mail | name | +---------+--------------------+-------+ | 7 | user7@leetcode.com | David | | 8 | user8@leetcode.com | Eve | | 9 | user9@leetcode.com | Frank | +---------+--------------------+-------+

💡 Note: David (user 7) qualifies because he won gold medals in contests 1, 3, and 4 (3+ gold medals total). Even though these weren't consecutive contests, he meets the gold medal criterion. Eve and Frank don't meet either qualification criterion.

example_3.sql — Consecutive Medal Winner

$ Input: Contests: +------------+------------+--------------+--------------+ | contest_id | gold_medal | silver_medal | bronze_medal | +------------+------------+--------------+--------------+ | 10 | 1 | 2 | 3 | | 11 | 4 | 1 | 5 | | 12 | 6 | 7 | 1 | | 13 | 8 | 9 | 10 | +------------+------------+--------------+--------------+ Users: +---------+--------------------+-------+ | user_id | mail | name | +---------+--------------------+-------+ | 1 | user1@leetcode.com | Grace | | 2 | user2@leetcode.com | Henry | +---------+--------------------+-------+

💡 Note: Grace (user 1) qualifies because she won medals in 3 consecutive contests (10, 11, 12): gold in contest 10, silver in contest 11, and bronze in contest 12. This demonstrates consistent performance across consecutive competitions.

Visualization

Tap to expand

Understanding the Visualization

Raw Medal Data

User 1 has medals in contests 5, 6, 7 (consecutive)

Apply ROW_NUMBER()

Assign sequential numbers: 1, 2, 3 for each user's contests

Calculate Difference

contest_id - ROW_NUMBER(): (5-1)=4, (6-2)=4, (7-3)=4

Group by Difference

Same difference means consecutive! Group by user + difference value

Count Sequences

HAVING COUNT(*) >= 3 finds sequences of 3+ consecutive contests

Key Takeaway

🎯 Key Insight: The magic of `contest_id - ROW_NUMBER()` creates identical values for consecutive sequences, enabling efficient grouping and counting in a single pass!

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass with sorting for window functions, much faster than nested queries

⚡ Linearithmic

Space Complexity

O(n)

Memory for CTEs and intermediate window function calculations

⚡ Linearithmic Space

Constraints

1 ≤ contests.length ≤ 100
1 ≤ contest_id ≤ 1000
Contest IDs are consecutive with no gaps
1 ≤ user_id ≤ 10⁴
All medal winner user_ids exist in Users table
No ties - each contest has exactly 3 different winners

Asked in

G Google 45 f Meta 38 a Amazon 32 ⊞ Microsoft 28

The optimal solution uses Common Table Expressions (CTEs) with window functions to efficiently identify interview candidates. Key insight: use ROW_NUMBER() to detect consecutive sequences by subtracting row numbers from contest IDs - consecutive contests will have the same difference value. This enables single-pass processing of both qualification criteria.

Common Approaches

Approach	Time	Space	Notes
✓ Optimized CTE with Window Functions	O(n log n)	O(n)	Use Common Table Expressions and window functions for efficient single-pass solution
Nested Query Approach	O(n²)	O(n)	Use multiple subqueries to check each condition separately

Optimized CTE with Window Functions — Algorithm Steps

Create CTE to normalize all medals into single table
Use window functions to identify consecutive sequences
Apply aggregation to find qualified users for both conditions
Join with Users table once for final result

Visualization

Tap to expand

Step-by-Step Walkthrough

All Medals CTE

Normalize all medal types into unified structure

Window Function

Use ROW_NUMBER() to detect consecutive sequences

Parallel Processing

Calculate both conditions simultaneously

Final Join

Single join with Users table for result

Code -

solution.c — C

#include <stdio.h>

void printOptimalQuery() {
    printf("Optimized SQL Query:\n");
    printf("WITH all_medals AS (\n");
    printf("    SELECT contest_id, gold_medal as user_id, 'gold' as medal_type FROM Contests\n");
    printf("    UNION ALL\n");
    printf("    SELECT contest_id, silver_medal as user_id, 'silver' as medal_type FROM Contests\n");
    printf("    UNION ALL\n");
    printf("    SELECT contest_id, bronze_medal as user_id, 'bronze' as medal_type FROM Contests\n");
    printf("),\n");
    printf("consecutive_medals AS (\n");
    printf("    SELECT user_id, contest_id,\n");
    printf("           contest_id - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY contest_id) as sequence_group\n");
    printf("    FROM all_medals\n");
    printf("),\n");
    printf("consecutive_candidates AS (\n");
    printf("    SELECT DISTINCT user_id FROM consecutive_medals\n");
    printf("    GROUP BY user_id, sequence_group HAVING COUNT(*) >= 3\n");
    printf("),\n");
    printf("gold_candidates AS (\n");
    printf("    SELECT gold_medal as user_id FROM Contests\n");
    printf("    GROUP BY gold_medal HAVING COUNT(*) >= 3\n");
    printf("),\n");
    printf("all_candidates AS (\n");
    printf("    SELECT user_id FROM consecutive_candidates\n");
    printf("    UNION SELECT user_id FROM gold_candidates\n");
    printf(")\n");
    printf("SELECT u.name, u.mail FROM Users u\n");
    printf("INNER JOIN all_candidates ac ON u.user_id = ac.user_id\n");
    printf("ORDER BY u.name;\n");
}

int main() {
    printOptimalQuery();
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass with sorting for window functions, much faster than nested queries

⚡ Linearithmic

Space Complexity

O(n)

Memory for CTEs and intermediate window function calculations

⚡ Linearithmic Space

Constraints

1 ≤ contests.length ≤ 100
1 ≤ contest_id ≤ 1000
Contest IDs are consecutive with no gaps
1 ≤ user_id ≤ 10⁴
All medal winner user_ids exist in Users table
No ties - each contest has exactly 3 different winners

38.2K Views

High Frequency

~25 min Avg. Time

1.5K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Optimized CTE with Window Functions — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler