[SQL] solvesql 코딩테스트 연습2

문제1.  멘토링 짝꿍 리스트

출처 : solvesql 연습문제

문제 정보 : 난이도 3 / 정답률 40.68%

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

 

정답 쿼리

SELECT a.employee_id AS mentee_id, a.name AS mentee_name, b.employee_id AS mentor_id, b.name AS mentor_name
FROM employees a
cross join employees b
WHERE a.join_date BETWEEN '2021-10-01' and '2021-12-31'
AND b.join_date <= '2019.12.31'
AND a.department != b.department
ORDER BY mentee_id, mentor_id

 

문제 해설

1. 주요 포인트 1 - cross join

FROM employees a
cross join employees b

 

같은 테이블에 속한 직원들을 각각 다른 기간을 기준으로 멘토, 멘티로 구분해야 한다. 따라서 cross join을 통해 멘토멘티로 조합이 가능한 모든 경우의 수를 우선적으로 출력한다.

 

2. 주요 포인트 2 - where 절

WHERE a.join_date BETWEEN '2021-10-01' and '2021-12-31'
AND b.join_date <= '2019.12.31'
AND a.department != b.department

 

설정한 alias에 맞게 멘티일 경우 날짜 기준, 멘토일 경우 날짜 기준 조건을 각각 걸어주고 멘토와 멘티는 각각 다른 부서여야 하기 때문에 총 3개의 조건절을 걸어준다.

---

문제2.  배송 예정일 예측 성공과 실패

출처 : solvesql 데이터리안 sql캠프 실전반

문제 정보 : 난이도 3 / 정답률 40.83%

https://solvesql.com/problems/estimated-delivery-date/

 

정답 쿼리

SELECT 
    strftime('%Y-%m-%d', o.order_purchase_timestamp) AS purchase_date,
    COUNT(CASE WHEN o.order_estimated_delivery_date >= o.order_delivered_customer_date THEN o.order_id END) AS success,
    COUNT(CASE WHEN o.order_estimated_delivery_date < o.order_delivered_customer_date THEN o.order_id END) AS fail
FROM olist_orders_dataset AS o
WHERE o.order_purchase_timestamp like '2017-01%'
      AND o.order_delivered_customer_date IS NOT NULL 
      AND o.order_estimated_delivery_date IS NOT NULL
GROUP BY purchase_date
ORDER BY purchase_date;

 

문제 해설

 

1. 주요 포인트 1 - select 절

SELECT 
    strftime('%Y-%m-%d', o.order_purchase_timestamp) AS purchase_date,
    COUNT(CASE WHEN o.order_estimated_delivery_date >= o.order_delivered_customer_date THEN o.order_id END) AS success,
    COUNT(CASE WHEN o.order_estimated_delivery_date < o.order_delivered_customer_date THEN o.order_id END) AS fail

 

mysql이 아닌 sqlite의 경우 date_format이 아닌 strftime으로 날짜 포맷을 변경한다.(함수가 다를 뿐 결과값은 동일)

또한 case when then 구문과 count 집계함수를 통해 배송예정일과 실제 배송날짜 간 두가지 케이스를 구분하여 작성해준다.

 

필자의 경우 스칼라 서브쿼리를 처음에 사용했는데 sqlite의 경우 스칼라 서브쿼리에서 외부 쿼리의 컬럼을 참조하는 것이 지원되지 않아 다른 구문으로 바꿔 작성하였다.

 

2. 주요 포인트 2 - where 절

WHERE o.order_purchase_timestamp like '2017-01%'
      AND o.order_delivered_customer_date IS NOT NULL 
      AND o.order_estimated_delivery_date IS NOT NULL

 

2017년 1월 한 달 동안 발생한 주문만 다뤄야 하고, 배송예정일과 실제 배송날짜가 null값일 경우 출력에서 제외하기 때문에 총 3개의 조건절을 걸어준다.