Data Scientist Core Skill Shortlist

Easy

A recruiting analytics team tracks candidate capabilities in a normalized skill table. Table: Candidates candidate_id: integer identifier of the candidate skill: text label for one declared skill The pair (candidate_id, skill) is unique. A candidate qualifies for the Data Scientist shortlist only when all three required skills are present exactly as written: Python Tableau PostgreSQL Your task is to return the IDs of all qualifying candidates. Output requirements: Return exactly one column: candidate_id Return each candidate only once Sort rows by candidate_id in ascending order Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

Candidates:
| candidate_id | skill        |
|--------------|--------------|
| 1001         | Python       |
| 1001         | Tableau      |
| 1001         | PostgreSQL   |
| 1001         | Spark        |
| 1002         | Python       |
| 1002         | Tableau      |
| 1003         | PostgreSQL   |
| 1004         | Python       |
| 1004         | Tableau      |
| 1004         | PostgreSQL   |

Output

[
  {"candidate_id":1001},
  {"candidate_id":1004}
]

Explanation

Candidates 1001 and 1004 have all three required skills. Extra skills like Spark are allowed. Candidate 1002 is missing PostgreSQL, and 1003 is missing Python and Tableau.

Example 2

Input

Candidates:
| candidate_id | skill        |
|--------------|--------------|
| 201          | python       |
| 201          | Tableau      |
| 201          | PostgreSQL   |
| 202          | Python       |
| 202          | Tableau      |
| 202          | PostgreSQL   |
| 203          | Python       |
| 203          | PostgreSQL   |

Output

[
  {"candidate_id":202}
]

Explanation

Matching is case-sensitive and exact. The value 'python' does not satisfy the required skill 'Python'.

Example 3

Input

Candidates:
| candidate_id | skill        |
|--------------|--------------|
| 301          | Python       |
| 301          | Tableau      |
| 301          | PostgreSQL   |
| 302          | Python       |
| 302          | Tableau      |
| 303          | Python       |
| 303          | Tableau      |
| 303          | PostgreSQL   |
| 304          | Tableau      |
| 304          | PostgreSQL   |

Output

[
  {"candidate_id":301},
  {"candidate_id":303}
]

Explanation

Only candidates with all three mandatory skills are returned, sorted by candidate_id.

Constraints

1 <= number of rows in Candidates <= 50,000
1 <= candidate_id <= 1,000,000,000
1 <= length(skill) <= 100
(candidate_id, skill) is unique within each test case
Skill matching is exact and case-sensitive
Additional non-required skills may be present
Return exactly one column: candidate_id
Sort output by candidate_id ASC
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with column [candidate_id]

Candidates: | candidate_id | skill | |--------------|--------------| | 1001 | Python | | 1001 | Tableau | | 1001 | PostgreSQL | | 1001 | Spark | | 1002 | Python | | 1002 | Tableau | | 1003 | PostgreSQL | | 1004 | Python | | 1004 | Tableau | | 1004 | PostgreSQL |

Candidates: | candidate_id | skill | |--------------|--------------| | 201 | python | | 201 | Tableau | | 201 | PostgreSQL | | 202 | Python | | 202 | Tableau | | 202 | PostgreSQL | | 203 | Python | | 203 | PostgreSQL |

Candidates: | candidate_id | skill | |--------------|--------------| | 301 | Python | | 301 | Tableau | | 301 | PostgreSQL | | 302 | Python | | 302 | Tableau | | 303 | Python | | 303 | Tableau | | 303 | PostgreSQL | | 304 | Tableau | | 304 | PostgreSQL |