A simple MySQL implementation without variables and only requiring DATEDIFF():

SET sql_mode = '';
SELECT Start_Date, End_Date
FROM 
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) a,
    (SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) b 
WHERE Start_Date < End_Date
GROUP BY Start_Date 
ORDER BY DATEDIFF(End_Date, Start_Date), Start_Date

Edits: Updated with the sql_mode='' based on @jakab922's comment. HackerRank changed the default sql_mode behavior to only_full_group_by at some point after I posted this.

Also, a full solution without the need to change modes was posted by @dougal_michael below, which uses the MIN() function instead.

Just adding this commented version. MySQL

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

For MS SQL users:

SELECT T1.Start_Date,T2.End_Date FROM ( SELECT Start_Date,ROW_NUMBER() OVER (ORDER BY Start_Date) RN FROM Projects WHERE Start_Date NOT IN (SELECT END_Date FROM Projects) ) AS T1 INNER JOIN (

SELECT End_Date,ROW_NUMBER() OVER (ORDER BY End_Date) RN FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)

) AS T2 ON T1.RN = T2.RN ORDER BY DATEDIFF(Day,T1.Start_Date,T2.End_Date),T1.Start_Date

For everyone:

The way to solve this problem is to find Start_date that is not in End_Date and End_Date that is not in Start_Date.

Why? because if a Start_date is not in EndDate, it means it is a new project. And if a End_Date is not in Start_Date, it means this End_Date is the End_Date of a project.

We Gerenate a row number and Order by start_date so that it can match the Project's EndDate.

Same logic, We Gerenate a row number and Order by End_date so that it can match the Project's StartDate.

If a rownumber from Start_Date match the rownumber from End_Date, they must be the start_Date and End_Date in the same project.

think about it.

This is more simpler solution using "TABIBITOSAN METHOD"

select sd,ed from ( select MIN(start_date) as sd,MAX(end_date) as ed,(MAX(end_date)- MIN(start_date)) as day_diff from ( select end_date-1 as start_date ,end_date as end_date ,row_number() over (order by end_date) as rn ,end_date-row_number() over (order by end_date) as end_date_grp from projects ) GROUP BY end_date_grp ORDER BY 3,1 );

You can refer the link to understand what is tabibitosan method, https://community.oracle.com/docs/DOC-915680

Thanks, Madhan

My simple solution in SQL Server:

;WITH CTE_Projects AS 
(
  SELECT Start_Date, End_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) AS RowNumber
  FROM Projects WITH (NOLOCK)
)
SELECT MIN(Start_Date), MAX(End_Date) 
FROM CTE_Projects WITH (NOLOCK)
GROUP BY DATEDIFF(DAY, RowNumber, Start_Date)
ORDER BY DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)), MIN(Start_Date)

The solution query can be acheived very easily without using any JOIN so I wonder why the Creator of this Challenge had placed this under the "Advanced Join" section