[DataLemur] 특정 skill을 가진 candidate 찾기

https://datalemur.com/questions/matching-skills

LinkedIn SQL Interview Question | DataLemur

 

테이블 (candidates)

 

목표

• find the candidates (who posses Python, Tableau, PostgreSQL skill)

쿼리1

• where절에서 3개의 스킬만 거른다.
• group by, having을 사용하여 3개 스킬을 가진 candidate_id만 결과로 반환

 

SELECT candidate_id
FROM candidates
WHERE skill IN ('Python','Tableau','PostgreSQL')
GROUP BY candidate_id
HAVING count(distinct skill) = 3
ORDER BY 1

 

 

쿼리2

• GROUP_CONCAT() + GROUP BY 를 사용-> candidate 별로 가지고 있는 skill을 한 row로 concat한다. 

WITH CTE AS (
  SELECT *, group_concat(skill order by skill) as skills
  FROM candidates
  GROUP BY candidate_id
)

SELECT candidate_id
FROM CTE
WHERE skills = 'PostgreSQL,Python,Tableau'