Find Candidates for Data Scientist Position - Problem

You are given a table Candidates containing candidate information with their skills. Your task is to find candidates who are qualified for a Data Scientist position.

A candidate is qualified if they have all three required skills:

  • Python
  • Tableau
  • PostgreSQL

Return the result ordered by candidate_id in ascending order.

Table Schema

Candidates
Column Name Type Description
candidate_id PK int Unique identifier for each candidate
skill PK varchar Skill possessed by the candidate
Primary Key: (candidate_id, skill)
Note: Each row represents one skill for a candidate. A candidate can have multiple skills across multiple rows.

Input & Output

Example 1 — Basic Candidate Filtering
Input Table:
candidate_id skill
101 Java
101 Python
101 MySQL
101 PostgreSQL
101 Tableau
102 Python
102 MySQL
103 Python
103 PostgreSQL
Output:
candidate_id
101
💡 Note:

Candidate 101 has all three required skills (Python, Tableau, PostgreSQL) plus additional skills. Candidate 102 only has Python, and candidate 103 has Python and PostgreSQL but missing Tableau.
Example 2 — Multiple Qualified Candidates
Input Table:
candidate_id skill
201 Python
201 Tableau
201 PostgreSQL
202 Python
202 Tableau
202 PostgreSQL
202 R
203 Python
203 Java
Output:
candidate_id
201
202
💡 Note:

Both candidates 201 and 202 have all three required skills. Candidate 202 has an additional R skill. Candidate 203 is missing Tableau and PostgreSQL. Results are ordered by candidate_id.
Example 3 — No Qualified Candidates
Input Table:
candidate_id skill
301 Python
301 Java
302 Tableau
302 Excel
303 PostgreSQL
303 MySQL
Output:
candidate_id
💡 Note:

No candidate has all three required skills. Each candidate is missing at least two of the required skills for the Data Scientist position.

Constraints

  • 1 ≤ candidate_id ≤ 1000
  • skill is a non-empty string
  • Skills are case-sensitive
  • A candidate may have duplicate skill entries

Visualization

Tap to expand
Find Candidates for Data Scientist Position INPUT Candidates Table id name 101 Alice 102 Bob 103 Charlie Skills Table cand_id skill 101 Python 101 Tableau 101 PostgreSQL 102 Python 102 Tableau 103 Python Required Skills: Python, Tableau, PostgreSQL (All 3 required) ALGORITHM STEPS 1 Filter Required Skills WHERE skill IN ('Python', 'Tableau', 'PostgreSQL') 2 Group by Candidate GROUP BY candidate_id 3 Count Matching Skills HAVING COUNT(skill) = 3 4 Order Results ORDER BY candidate_id Skill Count per Candidate ID Count Status 101 3 OK 102 2 FAIL 103 1 FAIL FINAL RESULT Qualified Candidates candidate_id name 101 Alice A Alice (ID: 101) Data Scientist Candidate Python Tableau PostgreSQL 1 Candidate Found Output: [101] Key Insight: Use GROUP BY with HAVING COUNT(DISTINCT skill) = 3 to find candidates with ALL required skills. The HAVING clause filters groups after aggregation, ensuring only candidates with exactly 3 matching skills are returned. This is more efficient than multiple JOINs or subqueries for each skill. TutorialsPoint - Find Candidates for Data Scientist Position | Optimal Solution

Related Problems

Asked in
Amazon 28 Microsoft 22 Meta 18 Google 15
34.2K Views
Medium Frequency
~12 min Avg. Time
987 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