[SQL] HackerRank - SQL Project Planning(M)

  SQL Project Planning  

SQL Project Planning | HackerRank

 

---

# Problem

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

 

Explanation

The example describes following four projects:

 

* Project 1: Tasks 1, 2 and 3 are completed on consecutive days, so these are part of the project. Thus start date of project is 2015-10-01 and end date is 2015-10-04, so it took 3 days to complete the project.

* Project 2: Tasks 4 and 5 are completed on consecutive days, so these are part of the project. Thus, the start date of project is 2015-10-13 and end date is 2015-10-15, so it took 2 days to complete the project.

* Project 3: Only task 6 is part of the project. Thus, the start date of project is 2015-10-28 and end date is 2015-10-29, so it took 1 day to complete the project.

* Project 4: Only task 7 is part of the project. Thus, the start date of project is 2015-10-30 and end date is 2015-10-31, so it took 1 day to complete the project.

 

---

# Answer

1) 한 프로젝트의 시작일, 마감일 출력하기 ( 날짜가 연속적이라면 모두 한 프로젝트 )
2) 프로젝트 기간 순으로 정렬, 기간이 같다면 시작일 순으로 정렬하기

 

먼저, 프로젝트 시작일과 마감일을 찾아보자

• End_Date에 없는 Start_Date는 연속적이지 않고 개별적인 시작하는 프로젝트의 시작일이다.
• Start_Date에 없는 End_Date는 연속적이지 않고 개별적인 프로젝트의 마감일이다.

 

두 조건을 만족하는 쿼리를 작성하여 FROM 절에 넣어주면 다음과 같다.

SELECT *
  FROM (SELECT start_date
          FROM projects
         WHERE start_date NOT IN (SELECT end_date FROM projects)) AS s
     , (SELECT end_date
          FROM projects
         WHERE end_date NOT IN (SELECT start_date FROM projects)) AS e
 WHERE start_date < end_date
 ORDER BY start_date;

이 때 start_date는 end_date보다 빨라야 하므로 WHERE절에 조건을 넣어주어야 한다.

 

(1) 2015-10-01에 시작한 프로젝트의 마감일이 여러개가 나오는데 이 중 실제 마감일은 날짜가 가장 빠른 2015-10-05이다.

실제 마감일을 찾는 법은 start_date로 그룹화 한 뒤 end_date의 최솟값을 추출하는 것이다.

 

(2) 프로젝트 기간을 기준으로 정렬해야 하므로 DATEDIFF를 사용했다.

      DATEDIFF(날짜1, 날짜2) 가 의미하는 것은 날짜1 - 날짜2 이다.

 기간 다음으로는 start_date를 정렬 조건으로 지정하였다.

 

(3) 시작일, 마감일을 출력하기 위해 SELECT절에 지정해주었다.

SELECT start_date 
     , MIN(end_date)  -- (1)(3)
  FROM (SELECT start_date
          FROM projects
         WHERE start_date NOT IN (SELECT end_date FROM projects)) AS s
     , (SELECT end_date
          FROM projects
         WHERE end_date NOT IN (SELECT start_date FROM projects)) AS e
 WHERE start_date < end_date 
 GROUP BY start_date  -- (1)
 ORDER BY DATEDIFF(MIN(end_date), start_date), start_date;  -- (2)