Find Candidates for Data Scientist Position - Video Solutions

Problem Overview: You are given a table of candidate skills where each row represents a candidate and a single skill they possess. The goal is to return the IDs of candidates who qualify for a Data Scientist role by having all required skills: Python, Tableau, and PostgreSQL.

Approach 1: Multiple EXISTS Subqueries (O(n) time, O(1) space)

One direct approach checks each required skill using separate EXISTS conditions. For every candidate, run three correlated subqueries that verify whether rows exist for Python, Tableau, and PostgreSQL. If all three conditions evaluate to true, the candidate qualifies. This approach is straightforward and mirrors the logical requirement exactly. However, it performs repeated scans of the same table segments, which can become inefficient as the dataset grows.

Approach 2: Self Join on Skill Filters (O(n) time, O(1) space)

Another option joins the table with itself three times, each filtered to one required skill. For example, one alias filters rows with Python, another with Tableau, and the third with PostgreSQL. Joining them on candidate_id ensures the candidate appears in all three skill sets. This approach works well conceptually but introduces extra joins, which increases query complexity and can slow execution on larger tables.

Approach 3: Conditional Filtering + GROUP BY Aggregation (O(n) time, O(1) space)

The most efficient solution filters rows to only the required skills, then groups by candidate_id. Using GROUP BY with COUNT(DISTINCT skill) lets you verify whether a candidate has all required skills. If the count equals 3, that candidate possesses every required skill. This approach scans the table once, aggregates results per candidate, and avoids redundant joins or subqueries. It relies on core SQL aggregation patterns commonly used in SQL and aggregation problems.

Example idea:

SELECT candidate_id
FROM Candidates
WHERE skill IN ('Python','Tableau','PostgreSQL')
GROUP BY candidate_id
HAVING COUNT(DISTINCT skill) = 3;

This pattern—filter first, then aggregate—is common in database interview questions involving qualification checks or requirement matching.

Recommended for interviews: The conditional filtering with GROUP BY aggregation is the expected solution. It demonstrates strong SQL fundamentals and efficient thinking. Simpler approaches like multiple EXISTS checks show understanding of the requirement, but the aggregation approach proves you can transform row-level data into candidate-level statistics efficiently.