HackerRank SQL - SQL Project Planning

 

You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.

Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.

Sample Input

Problem

프로젝트를 완료하는데 걸린 일수를 start_date와 end_date를 추출하라.

단, 완료일수가 같은 프로젝트가 2개 이상인 경우 start_date를 기준으로 정렬, 만약 end_date의 값이 다음 task_id의 end_date와 1일 차이가 난다면 같은 프로젝트로 본다.

---

Answer1

SET @rownum :=0;
SELECT  MIN(T.start_date)
      , MAX(T.end_date)  
FROM
    (SELECT @rownum := @rownum+1
          , P.start_date
          , P.end_date
          , DATE_SUB(P.end_date, INTERVAL @rownum day) as group_date
    FROM Projects P
    ORDER BY P.start_date) T
GROUP BY T.group_date
ORDER BY DATEDIFF(MAX(T.end_date),MIN(T.start_date)), MIN(T.start_date)

풀어보고 나니 연속한 숫자를 찾는 쿼리와 비슷하다. row_number나 사용자 변수를 활용해 이런식으로 활용할 수 있다는 걸 깨닫게 된 문제였다. 

 

---

How to solve

처음에는 윈도우 함수를 붙여서 풀어보려고 했다.

end_date에서 1일을 더한 값과 그 다음 레코드의 동일 컬럼의 값이 일치할 경우 1을 반환하고 그렇지 않은 경우 0을 반환하는 쿼리문이다.

 

이걸로 같은 프로젝트인 경우를 묶어줄려고 했다. 

SELECT P.start_date, P.end_date, LEAD(p.end_date) OVER(ORDER BY P.start_date) lead_value
       ,IF(LAG(p.end_date) OVER(ORDER BY P.start_date) IS NULL, 0,
           IF(DATE_ADD(P.end_date, INTERVAL 1 DAY) = LEAD(p.end_date) OVER(ORDER BY P.start_date)
           ,1, 0))
FROM Projects P
ORDER BY P.start_date

여기까지는 결과가 잘 나왔는데 도무지 그다음 스텝이 떠오르질 않았다.

0이 나오면 시작 1이 나오면 연속, 다시 0이 나오면 해당 그룹의 끝이자, 또다른 그룹의 시작...

처음에 이런 구상을 하고 접근했고 곧 답이 없다는 걸 느꼈다.

 

그룹을 묶는 건 다른 접근이 필요했다.

우선 핵심은 연속된 값을 어떻게 찾아내는지, 어떻게 묶어주는지였는데

다행이도 선배님들이 해두신 레퍼런스들이 많았다.

 

http://modoleesi.blogspot.com/2017/12/blog-post.html

http://www.gurubee.net/lecture/2194

 

이걸 보는 순간,

감탄을 금치 못했다. 저렇게 사고의 틀을 깰 수 있구나.

 

우선 내가 좋아하는 방법은 아니지만, 변수를 만들어 rownum을 만들어 준다. 

여기서 잊으면 안되는 점은 order by 절이다. 여기서는 start_date 컬럼을 기준으로 오름차순으로 정렬해준다.

 

그리고 나서 rownum의 값을 end_date에 빼주는데 이렇게 하면 해당 값이 연속된 일자인지를 알 수 있게 된다.

아래와 같이 쿼리를 작성해준다.

SET @rownum :=0;
SELECT @rownum := @rownum+1
          , P.start_date
          , P.end_date
          , DATE_SUB(P.end_date, INTERVAL @rownum day) as group_date
FROM Projects P
ORDER BY P.start_date

 

좀 더 쉽게 풀어보면,

rownum은 레코드를 기준으로 연속적으로 1씩 증가하는데, 해당 값을 end_date에서 일자를 빼주면 연속된 end_date는 모두 같은 값을 가지게 된다.

해당 값이 무엇인지는 중요하지 않고 같은 값이 모두 묶였다는데 의미가 있다.

아래 데이터에서 5번째 레코드의 마지막 컬럼의 값은 '2015-10-07'인데 이 값은 start_date, end_date와는 아무런 관련이 없다. 바로 아래 6번째 레코드 역시 같은 값인데 이는 end_date가 연속적으로 이루어져있다는 것만 기억하면 된다.

rownum, start_date, end_date, group_date 순

 

그 다음은 간단하다.

해당 쿼리는 다시 서브쿼리로 묶어두고, group by로 group_date를 묶어준다.

select절에서는 해당 그룹 내에서 start_date는 최소값을, end_date는 최대값을 추출하면 된다.

SELECT  MIN(T.start_date)
       ,MAX(T.end_date)  
FROM
    (SELECT @rownum := @rownum+1
          , P.start_date
          , P.end_date
          , DATE_SUB(P.end_date, INTERVAL @rownum day) as group_date
    FROM Projects P
    ORDER BY P.start_date) T
GROUP BY T.group_date