[MySQL/Hackerrank] 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

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

 

 

문제)

각 태스크마다 시작일자와 종료일자가 저장되어 있는 테이블이 존재하고 두 일자의 차이는 무조건 1일이다.

만약 종료일자가 다른 태스크의 시작일자와 동일하다면 해당 태스크들은 모두 동일한 프로젝트의 일부라고 할 수 있다. 

이 때 테이블에 저장되어 있는 모든 프로젝트를 구하는데, 프로젝트 기간 오름차순, 기간이 동일할 경우 시작일자로 정렬해야 한다.

 

처음에는 그냥 Projects 테이블을 셀프조인했는데 도저히 프로젝트 중간에 있는 일자를 뺄 수가 없어서 엄청 헤맸다.

문제의 키는 프로젝트의 시작일자와 종료일자만 필요하기 때문에 중간 일자를 제거하는 것이다.

따라서 Projects 테이블을 셀프조인 할때 두 테이블 사이에 시작일자와 종료일자가 동일한 경우를 양쪽으로 제외해야 한다. 왜냐하면, 하나의 프로젝트는 연속적인 일자로 구성되므로 시작일자와 종료일자가 동일하다면 동일한 프로젝트이기 때문이다. 어렵다...ㅠㅜ

SELECT A.START_DATE, MIN(B.END_DATE)
FROM (
        SELECT START_DATE
        FROM Projects
        WHERE START_DATE NOT IN (SELECT END_DATE FROM Projects)
    ) A
    JOIN (
        SELECT END_DATE
        FROM Projects
        WHERE END_DATE NOT IN (SELECT START_DATE FROM Projects)
    ) B
    ON A.START_DATE < B.END_DATE
GROUP BY A.START_DATE
ORDER BY DATEDIFF(MIN(B.END_DATE),A.START_DATE), A.START_DATE