[HacKerRank SQL] SQL Project Planning - MYSQL

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.

 

아래의 조건으로 Start_Date와 End_Date 조회하기

- 특정 튜플의 End_Date가 다른 튜플의 Start_Date로 이어진다면, 이들을 연속된 프로젝트로 인식하여 조회하기

- 이를 총 프로젝트 일수의 내림차순, Start_Date 내림차순

 

 

예를들어, Projects 테이블이 다음과 같이 구성된 경우

Start_Date	End_Date
2021-03-03	2021-03-04
2021-03-04	2021-03-05
2021-03-05	2021-03-06
2021-03-01	2021-03-02

 

출력값은 다음과 같음

Start_Date	End_Date
2021-03-03	2021-03-06
2021-03-01	2021-03-02

 

SELECT
    Start_Date,
    MIN(End_Date)
FROM
    -- 프로젝트의 시작일만 추려냄
    (SELECT 
        Start_Date
     FROM 
        Projects
     WHERE 
        Start_Date NOT IN (SELECT End_Date FROM Projects)
    ) AS A,
    -- 프로젝트의 종료일만 추려냄
    (SELECT 
        End_Date
     FROM 
        Projects
     WHERE 
        End_Date NOT IN (SELECT Start_Date FROM Projects)
    ) AS B
    -- 종료일보다 빠른 시작일을 추려내되, 그룹핑을 통한, 종료일의 최솟값만 조회
WHERE 
    Start_Date < End_Date
GROUP BY 
    Start_Date
ORDER BY
    DATEDIFF(Start_Date, MIN(End_Date)) DESC, Start_Date