Find Candidates for Data Scientist Position

Find Candidates for Data Scientist Position - Problem

Database Easy

Find the Perfect Data Science Team!

You're a hiring manager at a tech company looking to build an elite data science team. You have a database of candidates and their technical skills, but you need to find those who have all three essential skills for a Data Scientist position.

Given a Candidates table where each row represents a candidate's skill, find all candidates who are proficient in Python, Tableau, and PostgreSQL.

Table: Candidates

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| candidate_id | int     |
| skill        | varchar |
+--------------+---------+

The combination of (candidate_id, skill) is the primary key.

Goal: Return the candidate_id of candidates who have all three required skills, ordered by candidate_id in ascending order.

Input & Output

example_1.sql — Basic Example

$ Input: Candidates table: +---------------+--------------+ | candidate_id | skill | +---------------+--------------+ | 101 | Python | | 101 | Tableau | | 101 | PostgreSQL | | 102 | Python | | 102 | Tableau | | 103 | Python | | 103 | Tableau | | 103 | PostgreSQL | | 104 | Tableau | +---------------+--------------+

› Output: +---------------+ | candidate_id | +---------------+ | 101 | | 103 | +---------------+

💡 Note: Candidate 101 has all three required skills (Python, Tableau, PostgreSQL). Candidate 102 is missing PostgreSQL. Candidate 103 has all three skills. Candidate 104 only has Tableau.

example_2.sql — No Qualified Candidates

$ Input: Candidates table: +---------------+--------------+ | candidate_id | skill | +---------------+--------------+ | 201 | Python | | 201 | Java | | 202 | Tableau | | 202 | Excel | | 203 | PostgreSQL | | 203 | MySQL | +---------------+--------------+

› Output: +---------------+ | candidate_id | +---------------+ (empty result)

💡 Note: No candidate has all three required skills. Each candidate only has one of the required skills along with other non-required skills.

example_3.sql — Single Qualified Candidate

$ Input: Candidates table: +---------------+--------------+ | candidate_id | skill | +---------------+--------------+ | 301 | Python | | 301 | Tableau | | 301 | PostgreSQL | | 301 | Java | | 301 | Excel | | 302 | Python | | 302 | Java | +---------------+--------------+

› Output: +---------------+ | candidate_id | +---------------+ | 301 | +---------------+

💡 Note: Candidate 301 has all three required skills plus additional skills (Java, Excel). Having extra skills doesn't disqualify them. Candidate 302 only has Python.

Constraints

1 ≤ candidate_id ≤ 1000
skill names are case-sensitive strings
Each (candidate_id, skill) combination is unique
Required skills are exactly: 'Python', 'Tableau', 'PostgreSQL'
Table contains 1 to 10⁴ rows

Visualization

Tap to expand

Understanding the Visualization

Collect Resumes

Gather all candidate skill records from the database

Filter Relevant Skills

Focus only on the three required skills: Python, Tableau, PostgreSQL

Group by Candidate

Organize filtered skills by each candidate

Count Skills

Count how many required skills each candidate has

Select Qualified

Choose candidates with exactly 3 required skills

Key Takeaway

🎯 Key Insight: Instead of checking each skill individually with separate queries, count the total required skills per candidate in one efficient GROUP BY operation!

Asked in

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

The optimal solution uses GROUP BY with COUNT to efficiently find candidates with all required skills in a single table scan. Filter for required skills first, then group by candidate_id and count skills - candidates with count = 3 qualify. This approach is much more efficient than multiple subqueries and scales well with additional skill requirements.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with COUNT (Optimal)	O(n)	O(k)	Use GROUP BY and COUNT to find candidates with exactly 3 required skills
Multiple Subqueries (Brute Force)	O(n³)	O(1)	Check each required skill separately using multiple subqueries

GROUP BY with COUNT (Optimal) — Algorithm Steps

Filter records to only include required skills (Python, Tableau, PostgreSQL)
Group the filtered results by candidate_id
Count the number of skills for each candidate
Select candidates where skill count equals 3
Order results by candidate_id ascending

Visualization

Tap to expand

Step-by-Step Walkthrough

Filter Skills

Filter table to only relevant skills in one pass

Group by Candidate

Group filtered records by candidate_id

Count Skills

Count skills per candidate group

Filter Count = 3

Select only candidates with exactly 3 skills

Code -

solution.c — C

// SQL Solution (Optimal Approach)
char* query = "\
SELECT candidate_id \
FROM Candidates \
WHERE skill IN ('Python', 'Tableau', 'PostgreSQL') \
GROUP BY candidate_id \
HAVING COUNT(skill) = 3 \
ORDER BY candidate_id;";

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the table with efficient grouping operation

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique candidates, needed for grouping

✓ Linear Space

28.4K Views

High Frequency

~12 min Avg. Time

856 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

Constraints

Visualization

Related Problems

Common Approaches

GROUP BY with COUNT (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler