[SQL테스트]멘토링 짝꿍 리스트

문제

https://solvesql.com/problems/mentor-mentee-list/

풀이

WITH mentee AS(
  SELECT employee_id, name, department, join_date,
    CASE WHEN join_date BETWEEN '2021-09-31' AND '2021-12-31' THEN 'mentee'
    END AS category
  FROM employees
  WHERE 1=1
  AND category IS NOT NULL
),
mentor AS(
  SELECT employee_id, name, department, join_date,
    CASE WHEN join_date<='2019-12-31' THEN 'mentor'
    END AS category
  FROM employees
  WHERE 1=1
  AND category IS NOT NULL
)
SELECT 
  A.employee_id AS mentee_id,
  A.name AS mentee_name,
  B.employee_id AS mentor_id,
  B.name AS mentor_name
FROM mentee A, mentor B
WHERE 1=1
AND A.department != B.department

후기

처음에는 날짜의 차이를 DIFFDATE로 진행했다가 SQLite에서는 지원하지 않는 함수라고 하길래

CASE WHEN (STRFTIME('%m', '2021-12-31')-STRFTIME('%m', join_date)) < 3 THEN 'mentee'

이런식으로 STRFTIME 함수를 사용했었다.
하지만 정답이 나오지 않자 직관적인 형태로 변환해서 코드 수정을했다.