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:
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:
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.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.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| GROUP BY + HAVING COUNT(DISTINCT) | O(n) | O(1) | Best general solution. Clean and efficient when verifying presence of multiple attributes. |
| Conditional Aggregation | O(n) | O(1) | Useful when each required condition must be checked explicitly. |
| Self Join per Skill | O(n) | O(n) | Educational approach showing relational joins, but less practical for large datasets. |
AMAZON LeetCode Medium 1811 “Find Interview Candidates" Interview SQL Question Explanation | EDS • Everyday Data Science • 1,713 views views
Watch 1 more video solutions →Practice Find Interview Candidates with our built-in code editor and test cases.
Practice on FleetCode