176번. SQL Project Planning

문제

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는 무조건 1일 간격
• 전날의 end_date와 당일의 start_date가 같은 경우는 consecutive한 케이스이며, 계속해서 날짜가 이어진다면 그것은 하나의 project로 간주됨
• 프로젝트의 시작일과 종료일을 SELECT
• order by 소요된 시간 asc, start_date asc

풀이 구상

연속되지 날짜를 찾아야하는 문제
ORDER BY start_date를 한 뒤 LAG() 함수를 사용하면 오늘의 start_date와 전날의 end_date를 비교할 수 있으며, 이 둘이 '같다면' 연속되는 것이라고 볼 수 있다.
즉, 반대로 말해 이 둘이 같지 않은 경우는 연속되지 않은 것이고, 그 첫번째 날짜들만 취한다면 그것이 '프로젝트별 첫번째 날짜'가 된다.

반대로, 방금 LAG()를 통해 옮겼던 값을 다시 LEAD()를 통해 가져온다면, 그 값은 '프로젝트별 마지막 날짜'가 된다. 애초에 order by를 하여 마지막에 있던 row의 날짜를 가져왔던 것이기에.

코드

WITH find_dates AS (
  SELECT
      *
      , CASE 
          WHEN lead(yesterday_end_date) over () IS NULL THEN end_date
          ELSE lead(yesterday_end_date) over ()
      END as "project_end_date"
  FROM (
  SELECT
      *
      , lag(end_date) over () as 'yesterday_end_date'
  FROM
      Projects
  ORDER BY
      start_date
  ) aa
  WHERE 
      (yesterday_end_date IS NULL)
      OR (start_date != yesterday_end_date)
)
SELECT
    start_date
    , project_end_date
FROM 
    find_dates
ORDER BY
    datediff(project_end_date, start_date)
    , start_date
;

알게된 점

처음에는 문제를 잘못 이해하여 하나의 프로젝트가 끝난 뒤 다음날 바로 다음 프로젝트를 이어서 한다고 생각했다
그렇게 하면 오히려 전체 날짜가 하나의 프로젝트로 묶여야 함에도 불구하고.
문제를 잘 읽고 이해하는 것이 역시 중요하지 않았나...

또한 중간중간 실행을 해보면서 진행했기에 발견했지만, LAG()나 LEAD() 함수를 사용하면 첫번째/마지막 row에는 NULL값을 취하게 된다.
그렇기에 LAG나 LEAD를 사용할 때는 IS NULL을 사용하여 첫번째/마지막 row에 적용될 수 있도록 조건을 걸어주는 것이 필요하다.

다른 답안

프로젝트 시작일과 종료일을 각각 구하고, row_number를 통해 같은 프로젝트끼리 묶어주는 방식

WITH A AS (
	SELECT 
		Start_Date
		, ROW_NUMBER() OVER(ORDER BY Start_Date) as "SD"
   FROM 
	   projects
   WHERE 
	   Start_Date NOT IN (SELECT End_Date FROM projects)
)
, B AS(
	SELECT 
		End_Date
		, ROW_NUMBER() OVER(ORDER BY Start_Date) AS "ED"
  FROM 
	  projects
  WHERE 
	  End_Date NOT IN (SELECT Start_Date FROM projects)
)
SELECT 
	A.Start_Date
	, B.End_Date
FROM 
	A 
	JOIN B
		ON A.SD = B. ED
ORDER BY DATEDIFF(B.End_Date,A.Start_Date), 1;