Find Candidates for Data Scientist Position

Find Candidates for Data Scientist Position

What is this problem about?

The Find Candidates for Data Scientist Position coding problem is a SQL-based filtering task. You are given a table Candidates that contains candidate IDs and their skills. Your goal is to identify all candidates who possess all three required skills for a Data Scientist role: 'Python', 'Tableau', and 'PostgreSQL'. The output should be a list of candidate IDs, sorted in ascending order.

Why is this asked in interviews?

This "Easy" level question is a frequent flyer at HashedIn and other data-focused firms. it's a test of your foundational SQL interview pattern skills, specifically your ability to perform grouping and set-based filtering. It evaluations whether you can correctly use the GROUP BY clause in combination with HAVING to ensure that a specific count of distinct attributes is met for each group.

Algorithmic pattern used

This problem follows the Relational Division or Group-By with Filtering pattern.

1. Use a WHERE clause to filter the rows to only include the three required skills.
2. Group the results by candidate_id.
3. Use the HAVING clause to check if the count of unique skills for that candidate is exactly 3.
4. Sort the resulting IDs.

Example explanation

Table Candidates:

candidate_id	skill
1	Python
1	Tableau
1	PostgreSQL
2	Python
3	Python
3	Tableau

1. Filter for the 3 skills: Candidate 1 has 3 matches, Candidate 2 has 1 match, Candidate 3 has 2 matches.
2. Group by ID and Count:
   • ID 1: Count = 3.
   • ID 2: Count = 1.
   • ID 3: Count = 2.
3. Keep only count = 3. Result: [1].

Common mistakes candidates make

• Using OR without counting: Simply selecting candidates with 'Python' OR 'Tableau' OR 'PostgreSQL' will include people who only have one of them.
• Missing the HAVING clause: Trying to filter the count in the WHERE clause, which is not allowed for aggregate functions.
• Forgetting DISTINCT: If the table allows duplicate (id, skill) pairs, a candidate might have 'Python' listed twice and still pass the count check unless you use COUNT(DISTINCT skill).

Interview preparation tip

Whenever you see a problem asking for "entities that satisfy ALL conditions," think about grouping by the entity and checking the count of satisfied conditions. This is a very common template for "all-match" queries.