Welcome to Subscribe On Youtube
3051. Find Candidates for Data Scientist Position
Description
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.
Solutions
Solution 1: Conditional Filtering + Grouping Statistics
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;