Skip to main content
Talentd

Find Interview Candidates - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase5 min readAsked at: Amazon
Practice this problem

Problem Statement

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 LeetCode 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.

 

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.

The result format is in the following example.

 

Example 1:

Input: 
Contests table:
+------------+------------+--------------+--------------+
| contest_id | gold_medal | silver_medal | bronze_medal |
+------------+------------+--------------+--------------+
| 190        | 1          | 5            | 2            |
| 191        | 2          | 3            | 5            |
| 192        | 5          | 2            | 3            |
| 193        | 1          | 3            | 5            |
| 194        | 4          | 5            | 2            |
| 195        | 4          | 2            | 1            |
| 196        | 1          | 5            | 2            |
+------------+------------+--------------+--------------+
Users table:
+---------+--------------------+-------+
| user_id | mail               | name  |
+---------+--------------------+-------+
| 1       | sarah@leetcode.com | Sarah |
| 2       | bob@leetcode.com   | Bob   |
| 3       | alice@leetcode.com | Alice |
| 4       | hercy@leetcode.com | Hercy |
| 5       | quarz@leetcode.com | Quarz |
+---------+--------------------+-------+
Output: 
+-------+--------------------+
| name  | mail               |
+-------+--------------------+
| Sarah | sarah@leetcode.com |
| Bob   | bob@leetcode.com   |
| Alice | alice@leetcode.com |
| Quarz | quarz@leetcode.com |
+-------+--------------------+
Explanation: 
Sarah won 3 gold medals (190, 193, and 196), so we include her in the result table.
Bob won a medal in 3 consecutive contests (190, 191, and 192), so we include him in the result table.
    - Note that he also won a medal in 3 other consecutive contests (194, 195, and 196).
Alice won a medal in 3 consecutive contests (191, 192, and 193), so we include her in the result table.
Quarz won a medal in 5 consecutive contests (190, 191, 192, 193, and 194), so we include them in the result table.

 

Follow up:

  • What if the first condition changed to be "any medal in n or more consecutive contests"? How would you change your solution to get the interview candidates? Imagine that n is the parameter of a stored procedure.
  • Some users may not participate in every contest but still perform well in the ones they do. How would you change your solution to only consider contests where the user was a participant? Suppose the registered users for each contest are given in another table.

Approach Overview

Problem Overview: The table Candidates stores a candidate ID and one skill per row. You need to return candidates who meet the interview requirement: they must possess all required skills (Python, Tableau, and PostgreSQL). The result should list the qualifying candidate_id values.

Approach 1: GROUP BY + HAVING with COUNT(DISTINCT) (O(n) time, O(1) space)

Aggregate rows by candidate_id and count how many required skills each candidate has. First filter rows to the relevant skill set using WHERE skill IN (...). Then apply GROUP BY candidate_id and enforce the requirement with HAVING COUNT(DISTINCT skill) = 3. The key insight: each required skill appears as a separate row, so counting distinct skills directly verifies whether a candidate covers the entire requirement set. This approach performs a single scan of the filtered rows and relies on SQL aggregation to enforce the constraint.

Approach 2: Conditional Aggregation (O(n) time, O(1) space)

Group by candidate_id and compute conditional counts for each required skill using expressions like SUM(skill = 'Python'). Repeat for Tableau and PostgreSQL. In the HAVING clause, require each count to be greater than zero. This pattern explicitly checks the presence of every required skill and is commonly used when working with database interview problems involving categorical attributes.

Approach 3: Self Join on Skills (O(n) time, O(n) space)

Join the Candidates table with itself three times, once per required skill. Each join filters a specific skill (Python, Tableau, PostgreSQL) while matching the same candidate_id. If a candidate appears in all three joins, they possess every required skill. While straightforward conceptually, this approach creates multiple intermediate join results and is less efficient than aggregation. It is mainly useful for demonstrating relational join reasoning in SQL or database interviews.

Recommended for interviews: The GROUP BY + HAVING COUNT(DISTINCT) solution is the cleanest and most efficient. It scans the table once, expresses the requirement clearly, and scales well as the dataset grows. Interviewers expect candidates to recognize that skill presence can be validated through aggregation rather than multiple joins.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
GROUP BY + HAVING COUNT(DISTINCT)O(n)O(1)Best general solution. Clean and efficient when verifying presence of multiple attributes.
Conditional AggregationO(n)O(1)Useful when each required condition must be checked explicitly.
Self Join per SkillO(n)O(n)Educational approach showing relational joins, but less practical for large datasets.

Video Solution

AMAZON LeetCode Medium 1811 “Find Interview Candidates" Interview SQL Question Explanation | EDS • Everyday Data Science • 1,773 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Find Interview Candidates easy or hard?
Find Interview Candidates is considered a medium-level SQL problem. The challenge is recognizing that multiple rows represent skills for the same candidate and using aggregation (GROUP BY and HAVING) to verify that all required skills are present.
Find Interview Candidates Python/Java solution
This problem is typically solved with SQL because it operates directly on relational tables. In application languages like Python or Java, you would replicate the logic using a hash map from candidate_id to a set of skills and then filter candidates whose set contains all required skills.
How to solve Find Interview Candidates in O(n)?
Filter the table with WHERE skill IN ('Python','Tableau','PostgreSQL'), then GROUP BY candidate_id. Use HAVING COUNT(DISTINCT skill) = 3 to ensure all required skills are present. This approach scans the table once and uses aggregation to validate the requirement efficiently.
What is the best approach for Find Interview Candidates?
The best approach uses GROUP BY with HAVING COUNT(DISTINCT skill). Filter the table to the required skills (Python, Tableau, PostgreSQL), group rows by candidate_id, and keep candidates where the distinct skill count equals 3. This verifies that the candidate possesses every required skill with a single aggregation pass.
Is Find Interview Candidates asked at Google/Amazon/Meta?
SQL aggregation and filtering problems like this appear frequently in data and backend interview rounds at companies such as Amazon, Meta, and Google. The pattern of grouping entities and validating attribute coverage is common in analytics and product data queries.
What data structure is used in Find Interview Candidates?
The solution relies on relational database operations rather than traditional data structures. Internally, SQL engines use grouping structures (often hash-based) to aggregate rows by candidate_id and count distinct skills.
What is the time complexity of Find Interview Candidates?
The query runs in O(n) time where n is the number of rows in the Candidates table. The database performs a single scan of the filtered rows and aggregates them by candidate_id. Space complexity is O(1) aside from the grouping structure maintained by the database engine.

Ready to solve this problem?

Practice Find Interview Candidates with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary