[Hacker Rank] SQL Project Planning

📢 본 포스팅에 활용되는 기본 문제 및 자료 출처는 HackerRank 임을 밝힙니다.

https://www.hackerrank.com

---

■ 문제

https://www.hackerrank.com/challenges/sql-projects/problem

SQL Project Planning | HackerRank

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

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

 

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.

---

■ 풀이

문제 요구사항

> 프로젝트의 끝날짜(End_Date)가 연이어진다면, 그 행은 같은 프로젝트이다.

  사만다는 서로 다른 완료된 프로젝트의 총 개수를 구하길 원함.

  오름차순으로 완료된 일자의 프로젝트의 '시작 일자'와 '완료일자'를 출력하고

  만약 같은 일자에 완료된 프로젝트라면, 시작일자 순으로 나열할 것.

 

①  같은 프로젝트 찾기 > 프로젝트의 end_date가 다른 행의 start_date와 동일

②  완료된 프로젝트를 찾는다 > 프로젝트의 end_date가 다른 행의 start_date에 존재해선 안됨.

 

 

 

> start_date 추출 테이블	> End_Date 추출 테이블
SELECT Start_Date,                   ROW_NUMBER() over (ORDER BY Start_Date) rnk  FROM Projects  WHERE Start_Date NOT IN (SELECT DISTINCT End_Date                                                            FROM Projects)	SELECT end_date,             ROW_NUMBER() OVER (ORDER BY End_Date) rnk  FROM Projects  WHERE end_date NOT IN (SELECT DISTINCT Start_Date                                                          FROM Projects)
> 위 두 테이블을 rnk에 대해 inner join하면 각각 프로젝트가 끝나는 start_date와 end_date가 연결되고    총 프로젝트가 11개임을 확인할 수 있다.

 

③ > 완료 일자가 같으면 프로젝트 소요기간이 짧은 것부터 > 시작 일자가 빠른것 부터 나열

SELECT p_start_date.Start_Date, p_end_date.End_Date
 FROM (SELECT Start_Date, 
               ROW_NUMBER() over (ORDER BY Start_Date) rnk
         FROM Projects
         WHERE Start_Date NOT IN (SELECT DISTINCT End_Date
                                   FROM Projects)) p_start_date
         INNER JOIN (
                     SELECT end_date,
                      ROW_NUMBER() OVER (ORDER BY End_Date) rnk
                      FROM Projects
                      WHERE end_date NOT IN (SELECT DISTINCT Start_Date 
                                              FROM Projects)) p_end_date
         ON p_start_date.rnk = p_end_date.rnk
 ORDER BY DATEDIFF(End_date, Start_Date), Start_Date;