오늘도 데이터분석

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.

End_Date가 연속적이면(=다음 start_date와 같으면) 같은 프로젝트이다.
서로 다른 프로젝트의 start, end date가 나오도록 쿼리 입력.
결과는 프로젝트 걸린 기간이 작은 순대로, 기간이 동일할 경우 start date 순 대로 정렬

=>  start date가 end date에 없으면 개별 프로젝트가 새로 시작하는(연속이 끊기는) start date
      end date가 start date에 없으면 같은 프로젝트가 끝나는(연속이 끊기는) end date

풀이

1. end date에 없는 start date, start date에 없는 end date 추출

2. 시작날짜가 종료날짜보다 작아야 함

3. 시작날짜 기준으로 그룹화, 종료날짜는 최소날짜

4. 종료날짜와 시작날짜 차이 계산 -> 정렬, 차이 동일하면 start date기준 정렬

* 종료날짜와 시작날짜 차이 계산

   DATEDIFF 함수 사용

DATEDIFF : 두 날짜 사이의 일수 계산
DATEDIFF(날짜1, 날짜2) => 날짜1 - 날짜2 값이 나옴 
*날짜 안에 시간이 포함되도 일수 계산만 나옴
*날짜 범위를 초과하면 (ex. 22-13-01) NULL 반환

최종 쿼리

SELECT start_date, MIN(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 start_date < end_date
GROUP BY start_date
ORDER by DATEDIFF(MIN(end_date), start_date), start_date