문제

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.

문제 해석

1. 서로 다른 프로젝트의 시작일과 완료일을 구하는 쿼리를 작성해라
2. Task의 완료일이 연속되었을 때 같은 프로젝트의 Task로 본다.
3. 프로젝트를 완료하는데 걸린 기간으로 정렬하고 시작일로 정렬한다.

문제 분석

• 시작일이 완료일에 포함되어있지 않고, 완료일이 시작일에 포함되지 않으면 프로젝트로 구분된다.
• 모든 잠재적 start_date, end_date 쌍을 구하고 그 중 가장 작은 end_date가 start_date와 쌍이 된다.
  • 위의 조건을 만족하면 후보군 중 가장 작은 end_date가 해당 프로젝트의 완료일이 된다.

정답

select start_date, min(end_date)
from
(select end_date from projects where end_date not in
(select start_date from projects)) as e,
(select start_date from projects where start_date not in
(select end_date from projects)) as s
where start_date < end_date
group by start_date
order by datediff(min(end_date),start_date), start_date