| comments | difficulty | edit_url | tags | |
|---|---|---|---|---|
true |
Easy |
|
Table: Candidates
+--------------+---------+ | Column Name | Type | +--------------+---------+ | candidate_id | int | | skill | varchar | +--------------+---------+ (candidate_id, skill) is the primary key (columns with unique values) for this table. Each row includes candidate_id and skill.
Write a query to find the candidates best suited for a Data Scientist position. The candidate must be proficient in Python, Tableau, and PostgreSQL.
Return the result table ordered by candidate_id in ascending order.
The result format is in the following example.
Example 1:
Input: Candidates table: +---------------+--------------+ | candidate_id | skill | +---------------+--------------+ | 123 | Python | | 234 | R | | 123 | Tableau | | 123 | PostgreSQL | | 234 | PowerBI | | 234 | SQL Server | | 147 | Python | | 147 | Tableau | | 147 | Java | | 147 | PostgreSQL | | 256 | Tableau | | 102 | DataAnalysis | +---------------+--------------+ Output: +--------------+ | candidate_id | +--------------+ | 123 | | 147 | +--------------+ Explanation: - Candidates 123 and 147 possess the necessary skills in Python, Tableau, and PostgreSQL for the data scientist position. - Candidates 234 and 102 do not possess any of the required skills for this position. - Candidate 256 has proficiency in Tableau but is missing skills in Python and PostgreSQL. The output table is sorted by candidate_id in ascending order.
First, we filter out candidates who have the skills Python, Tableau, and PostgreSQL. Then, we group by candidate_id and count the number of skills each candidate has. Finally, we filter out candidates who have these three skills and sort them in ascending order by candidate_id.
# Write your MySQL query statement below
SELECT candidate_id
FROM Candidates
WHERE skill IN ('Python', 'Tableau', 'PostgreSQL')
GROUP BY 1
HAVING COUNT(1) = 3
ORDER BY 1;