We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
CREATE TEMPORARY TABLE s_table(
(SELECT temp.start_d as std, @s_num:=@s_num + 1 AS snum FROM
(SELECT if(Start_Date not in (SELECT End_Date FROM Projects), Start_Date, NULL) as start_d
FROM Projects) temp
WHERE temp.start_d is NOT NULL) );
CREATE TEMPORARY TABLE e_table(
(SELECT temp.end_d as etd, @e_num:=@e_num + 1 AS enum FROM
(SELECT if(End_Date not in (SELECT Start_Date FROM Projects), End_Date, NULL) as end_d
FROM Projects) temp
WHERE temp.end_d is NOT NULL)
);
SELECT s.std, e.etd
FROM s_table AS s
JOIN
e_table AS e
ON s.snum = e.enum
ORDER BY datediff(e.etd, s.std) ASC
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
SQL Project Planning
You are viewing a single comment's thread. Return to all comments →
Solution in MYSQL
Pretty simple logic, but the solution looks complicated because of the use of two temporary tables.
SET @s_num = 0; SET @e_num = 0;
CREATE TEMPORARY TABLE s_table( (SELECT temp.start_d as std, @s_num:=@s_num + 1 AS snum FROM (SELECT if(Start_Date not in (SELECT End_Date FROM Projects), Start_Date, NULL) as start_d FROM Projects) temp WHERE temp.start_d is NOT NULL) );
CREATE TEMPORARY TABLE e_table( (SELECT temp.end_d as etd, @e_num:=@e_num + 1 AS enum FROM (SELECT if(End_Date not in (SELECT Start_Date FROM Projects), End_Date, NULL) as end_d FROM Projects) temp WHERE temp.end_d is NOT NULL) );
SELECT s.std, e.etd FROM s_table AS s JOIN e_table AS e ON s.snum = e.enum ORDER BY datediff(e.etd, s.std) ASC