SQL Project Planning — HackerRank Advanced SQL ( cool )

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.

Solution

SELECT start_date, 
       Min(end_date) 
FROM 
/* Choose start dates that are not end dates of other projects (if a start date is an end date, it is part of the samee project) */
(SELECT start_date 
 FROM   projects 
 WHERE  start_date NOT IN (SELECT end_date 
                           FROM   projects)) a, 
/* Choose end dates that are not end dates of other projects */ 
(SELECT end_date 
 FROM   projects 
 WHERE  end_date NOT IN (SELECT start_date 
                         FROM   projects)) b 
/* At this point, we should have a list of start dates and end dates that don't necessarily correspond with each other */
/* This makes sure we only choose end dates that fall after the start date, and choosing the MIN means for the particular start_date, we get the closest end date that does not coincide with the start of another task */
WHERE  start_date < end_date 
GROUP  BY start_date 
ORDER  BY Datediff(start_date, Min(end_date)) DESC, 
          start_date

Expected answer

2015-10-15 2015-10-16 
2015-10-17 2015-10-18 
2015-10-19 2015-10-20 
2015-10-21 2015-10-22 
2015-11-01 2015-11-02 
2015-11-17 2015-11-18 
2015-10-11 2015-10-13 
2015-11-11 2015-11-13 
2015-10-01 2015-10-05 
2015-11-04 2015-11-08 
2015-10-25 2015-10-31