[HackerRank] SQL Project Planning

 

 

[문제]

이미지를 누르면 문제 링크로 넘어갑니다

 

 

[MySQL]

WITH step1 AS (
    SELECT ROW_NUMBER() OVER (ORDER BY Start_Date) AS id
         , LAG(End_Date, 1) OVER (ORDER BY Start_Date) AS last_end
         , Start_Date
         , End_Date
         , LEAD(Start_Date, 1) OVER (ORDER BY Start_Date) AS next_start
    FROM Projects)
, step2 AS (
    SELECT id
         , CASE WHEN last_end != Start_Date THEN id
                WHEN last_end IS NULL THEN id
                ELSE LAG(id, 1) OVER (ORDER BY id)
            END AS project
         , Start_Date
         , End_Date
    FROM step1
    WHERE last_end IS NULL
       OR next_start IS NULL
       OR last_end != Start_Date
       OR End_Date != next_start)

SELECT MIN(Start_Date) AS start_date
     , MAX(End_Date) AS end_date
FROM step2
GROUP BY project
ORDER BY DATEDIFF(MAX(End_Date), MIN(Start_Date)) ASC, Start_Date ASC