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.
--MS SQL solution for beginer
WITH cte1 AS (
SELECT start_date, row_number() OVER(ORDER BY start_date) AS rowNumber
FROM projects
WHERE start_date NOT IN (SELECT end_date FROM projects)
),
cte2 AS (
SELECT end_date, row_number() OVER(ORDER BY end_date) AS rowNumber
FROM projects
WHERE end_date NOT IN (SELECT start_date FROM projects)
)
SELECT cte1.start_date, cte2.end_date
FROM cte1
INNER JOIN cte2 ON cte1.rowNumber = cte2.rowNumber
ORDER BY datediff(day, cte1.start_date, cte2.end_date) , cte1.start_date;
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 →
--MS SQL solution for beginer WITH cte1 AS ( SELECT start_date, row_number() OVER(ORDER BY start_date) AS rowNumber FROM projects WHERE start_date NOT IN (SELECT end_date FROM projects) ), cte2 AS ( SELECT end_date, row_number() OVER(ORDER BY end_date) AS rowNumber FROM projects WHERE end_date NOT IN (SELECT start_date FROM projects) ) SELECT cte1.start_date, cte2.end_date FROM cte1 INNER JOIN cte2 ON cte1.rowNumber = cte2.rowNumber ORDER BY datediff(day, cte1.start_date, cte2.end_date) , cte1.start_date;