Question

3051. Find Candidates for Data Scientist Position

English Version

题目描述

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.

解法

方法一：条件筛选 + 分组统计

我们首先筛选出具备 Python, Tableau, PostgreSQL 这三个技能的候选人，然后按照 candidate_id 进行分组统计，统计每个候选人具备的技能数量，最后筛选出具备这三个技能的候选人，并且按照 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;