[HackerRank] SQL Project Planning

SQL Project Planning | HackerRank

---

문제

 

문제 접근 방식

문제에서 요구하는 사항은 다음과 같다.

1) 프로젝트 종료일이 연속적이라면 동일한 프로젝트의 일부가 됨
2) 프로젝트 완료일수 기준 오름차순, 프로젝트 시작일자 기준 오름차순
3) 프로젝트 시작 날짜, 종료 날짜 출력

 

이 문제는 알고리즘 문제와 가까운 듯 하다.

lag, lead 함수 / datediff / rank 함수 등 여러 함수를 활용해보려고 했으나 결국 실패ㅠ

다른 사람들이 작성한 쿼리를 보고 이해함. 찾아보니 총 2개의 방법이 있었다.

#1. 알고리즘 문제 해결 방식

#2. ROW_NUMBER() 활용하기

 

 

 

#1. 알고리즘 문제 해결 방식

문제와 주어진 샘플 데이터를 확인해보면 다음과 같은 문제해결 방법이 있다.

1. End_Date에 없는 Start_Date는 연속적이지 않는 시작일
2. Start_Date에 없는 End_Date는 연속적이지 않은 종료일

 

이 아이디어만 잘 떠올렸다면 이것을 반영한 쿼리 짜는 건 시간문제.

 

1) 서브쿼리를 활용하여 위의 아이디어에 해당하는 시작일, 종료일을 가져온 후 조인하기

    단, 조인할 때 시작일이 종료일보다 작아야 한다는 조건 필수.

SELECT A1.START_DATE, A2.END_DATE
FROM (
    SELECT START_DATE
    FROM PROJECTS
    WHERE START_DATE NOT IN (SELECT END_DATE FROM PROJECTS)
) A1		-- End_Date에 없는 Start_Date는 연속적이지 않는 시작일
INNER JOIN (
    SELECT END_DATE
    FROM PROJECTS
    WHERE END_DATE NOT IN (SELECT START_DATE FROM PROJECTS)
) A2		-- Start_Date에 없는 End_Date는 연속적이지 않은 종료일
    ON A1.START_DATE < A2.END_DATE		-- 조건
;

 

 

 

◀ 위의 쿼리를 실행하면 좌측과 같이 1개의 시작일에 시작일보다 큰 종료일 전체가 붙는다.

시작일보다 큰 종료일 전체가 다 붙는 것이기 때문에, 우리는 그 중에서 가장 작은 종료일만을 활용하면 된다.

 

 

 

 

 

 

 

 

 

 

 

2) Start_Date 기준으로 가장 작은 End_Date 가져오기

SELECT A1.START_DATE, MIN(A2.END_DATE) AS MIN_END_DATE
FROM (
    SELECT START_DATE
    FROM PROJECTS
    WHERE START_DATE NOT IN (SELECT END_DATE FROM PROJECTS)
) A1
INNER JOIN (
    SELECT END_DATE
    FROM PROJECTS
    WHERE END_DATE NOT IN (SELECT START_DATE FROM PROJECTS)
) A2
    ON A1.START_DATE < A2.END_DATE
GROUP BY A1.START_DATE

 

 

3) 문제에서 요청한 조건인 프로젝트 완료까지 소요된 일수 기준 정렬, 동일하면 시작일자 기준 정렬. 소요 일수는 DATEDIFF 함수를 활용하면 됨

/* 최종 쿼리 */
SELECT A1.START_DATE, MIN(A2.END_DATE) AS MIN_END_DATE
FROM (
    SELECT START_DATE
    FROM PROJECTS
    WHERE START_DATE NOT IN (SELECT END_DATE FROM PROJECTS)
) A1
INNER JOIN (
    SELECT END_DATE
    FROM PROJECTS
    WHERE END_DATE NOT IN (SELECT START_DATE FROM PROJECTS)
) A2
    ON A1.START_DATE < A2.END_DATE
GROUP BY A1.START_DATE
ORDER BY DATEDIFF(MIN(A2.END_DATE), A1.START_DATE), A1.START_DATE
;

 

 

 

#2. ROW_NUMBER() 활용하기

정말 생각지도 못한 로직이다. 순위 함수를 생각하긴 했지만 어떻게 풀어나가야 할지 방법이 떠오르지 않았다.

이 로직은 연속된 날짜는 1씩 증가, ROW_NUMBER도 1씩 증가한다는 것을 잘 엮인 듯하다.

 

1) ROW_NUMBER로 시작일 기준으로 순위를 매긴 후, 시작일에서 순위를 빼기

SELECT START_DATE
     , END_DATE
     , ROW_NUMBER() OVER(ORDER BY START_DATE)
     , START_DATE - ROW_NUMBER() OVER(ORDER BY START_DATE) AS RN
FROM PROJECTS
;

 

 

 

- 날짜가 연속되어 있으면 시작일은 1씩 증가

- ROW_NUMBER()도 1씩 증가

▶ 그렇기에 시작일에서 순위를 빼주면 연속된 날짜들은 동일한 값을 갖게 된다.

(연속되어 있지 않으면 날짜는 N씩 증가하기 때문에 ROW_NUMBER와 증가값이 달라지게 됨)

 

 

 

 

 

2) RN을 기준으로 가장 작은 시작일과 가장 큰 종료일을 가져옴. 그리고 정렬은 위와 동일하게 datediff 함수 활용.

SELECT MIN(START_DATE), MAX(END_DATE)
FROM (
    SELECT START_DATE
         , END_DATE
         , START_DATE - ROW_NUMBER() OVER(ORDER BY START_DATE) AS RN
    FROM PROJECTS
) TB
GROUP BY RN
ORDER BY DATEDIFF(MAX(END_DATE), MIN(START_DATE)), MIN(START_DATE)
;