HackerRank - SQL Project Planning (서브쿼리, 윈도우함수)_MySQL

안녕하세요!

 

해커랭크의 SQL Project Planning 문제입니다.

 

- 문제 설명

각 프로젝트 별로 시작일, 종료일이 포함된 테이블이 있다.

시작일과 종료일은 각 행마다 1일 차이로 구성되어 있다.

종료일과 시작일이 연속된다면 해당 프로젝트는 동일한 프로젝트의 부분이다.

프로젝트의 시작일과 종료일을 출력해야 하며,

프로젝트가 완료되기까지 소요되는 기간을 기준으로 오름차순 정렬한다.

기간이 동일하다면 시작일 기준으로 정렬한다.

 

- 테이블

 

- 쿼리

SELECT s.start_date
     , e.end_date
FROM (
    SELECT start_date
         , ROW_NUMBER() OVER(ORDER BY start_date) rn
    FROM projects
    WHERE start_date NOT IN (SELECT end_date FROM projects)
    ) s
  INNER JOIN (
    SELECT end_date
         , ROW_NUMBER() OVER(ORDER BY end_date) rn
    FROM projects
    WHERE end_date NOT IN (SELECT start_date FROM projects)
    ) e
  ON s.rn = e.rn
ORDER BY DATEDIFF(e.end_date, s.start_date), s.start_date

1) 시작일의 순서를 매긴 테이블을 서브쿼리로 만든다. 이 때 조건은 시작일이 종료일과 겹치지 않아야 한다.

2) 종료일의 순서를 매긴 테이블을 서브쿼리로 만든다. 이 때 조건은 종료일과 시작일이 겹치지 않아야 한다.

 

위의 시작일, 종료일 서브쿼리 테이블을 만드는 이유는 일정이 연속되는 프로젝트를 찾기 위함이다.

시작일과 종료일이 같다면 연속되는 프로젝트이다.

따라서 날짜가 연속되는 경우를 제외하는 조건을 통해 각 프로젝트의 시작일과 종료일을 찾는다.

시작일 서브쿼리1)의 결과는 각 프로젝트의 최초 시작일을 나타내고,

종료일 서브쿼리2)의 결과는 각 프로젝트의 최후 종료일을 나타낸다.

 

3) 두 개의 서브쿼리를 윈도우함수(ROW_NUMBER)에서 구한 숫자가 동일한 행을 기준으로 조인시킨다.

4) 마지막으로 문제에서 요구한대로 종료일과 시작일의 차이를 기준으로 오름차순 정렬하고,
   차이의 결과가 동일한 경우는 시작일을 기준으로 오름차순 정렬한다.