Link to problem: https://www.hackerrank.com/challenges/projects/problem
The issue here is that we need to identify consecutive dates for the problem. Take a look at the sample data for this problem. Notice anything?
At first, we can clearly see that between the consecutive start and end dates there is one day’s difference. This is irrelevant though. Notice the dates that occur in only the start date column, and likewise for the end date column. Please excuse the crude drawing. Note that I have highlighted each unique value in each column. We can extract these dates by using the NOT IN clause. We can extract each of these unique dates in unique columns with separate SELECT statements that utilize a subquery to select the dates not in the adjacent column in the given table. This will ensure we select only nonconsecutive dates. Here is an example of what the SELECT statement would look like.
SELECT End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)
The problem here is we need to combine these two statements together with a JOIN. We can create a variable in order to assign a row number thereby creating a field to join the two SELECT statements together. Once done, we can extract what we need from both and ORDER the tables by the total amount of days between the start and end dates. We can do this by using the DATEDIFF function. Here is the end result of the query.
/*Set variables for row numbers*/
SET @row_num = 0;
SET @row_num1 = 0;
SELECT s1.Start_Date, s2.End_Date FROM
/*Select unique start dates*/
(SELECT @row_num := @row_num + 1 AS num, Start_Date FROM Projects WHERE Start_Date NOT IN (SELECT End_Date FROM Projects)) AS s1
/*Select unique end dates*/
(SELECT @row_num1 := @row_num1 + 1 AS num, End_Date FROM Projects WHERE End_Date NOT IN (SELECT Start_Date FROM Projects)) AS s2
ON s1.num = s2.num
/*Order by calculation of total # of days in between dates*/
ORDER BY DATEDIFF(s1.Start_Date, s2.End_Date) DESC, s1.Start_Date ASC