[해커랭크] [sql] SQL Project Planning

SQL Project Planning | HackerRank

SQL Project Planning | HackerRank

이번 문제는 어려워서 다른 분의 블로그를 참고하여 풀었습니다.

[HackerRank SQL] SQL Project Planning (tistory.com)

Sample Input

Sample Output

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

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.

 

문제: End_Date가 연속적인 경우는 같은 프로젝트로 생각하고, 완성된 개별적인 프로젝트를 찾는 것 

        각 프로젝트의 Start_Date와 End_Date를 출력하고 , 시간이 짧게 걸린 프로젝트부터 오름차순하여 출력한다.

         만약 프로젝트가 걸린 시간이 같아면, start_date가 먼저인 것부터 출력한다.

 

 

처음에는 lead 함수를 사용하여 다음행과 end_date의 차이가 1인 것을 조건을 주어 연속적인 것을 찾으려했다. 하지만 연속적인 것만 구하는 게 아니라, 연속된 프로젝트의 시작과 끝을 구하는 게 관건이므로 lead 함수로는 구할 수 없었다.

 

## 중요 포인트 

Start_Date가 End_Date에 없으면 연속된 게 아니므로 다른 프로젝트로 구분  End_Date가 Start_Date에 없으면 연속된 게 아니므로 다른 프로젝트로 구분

 

End_Date = Start_Date 인 경우 연속으로 이어지는 프로젝트이므로 역으로 생각하면 위의 조건을 성립하는 Start_Date, End_Date를 출력해야한다. 

 

-- 위의 조건에 맞는 Start_Date, End_Date를 가지고 와서 새로운 테이블을 생성해야 한다. (Case when으로 해봤지만, start_date, end_date 각 컬럼 당 따로 출력되어 조합 안이루어짐)

 

 

SELECT s.Start_Date, MIN(e.End_Date)
FROM ( SELECT Start_Date
     FROM Projects 
     WHERE Start_Date NOT IN (select End_Date from Projects) ) s ,
     (SELECT End_Date
     FROM Projects
     WHERE End_Date NOT IN (select Start_Date from Projects)) e
WHERE s.Start_Date < e.End_Date
GROUP BY s.Start_Date
ORDER BY DATEDIFF(MIN(e.End_Date),s.Start_Date), s.Start_Date

 

 

-- 그냥 cross join을 하면 모든 조합이 다 만들어지므로 start_date로 그룹바이를 해주고, end_date를 가장 작은 걸 갖고와야 start_date에 따른 가장 작은 날짜의 end_date가 출력

 

-- 프로젝트가 걸린 일수가 작은 것부터 출력해야 하므로 DATEDIFF 함수를 사용하여 end_date와 start_date의 차이 순으로 order by, 프로젝트 걸린 일수가 같으면 start_date 가 빠른 순으로 출력되게 함