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.
WITH cte1 AS (
SELECT
start_date,
ROW_NUMBER() OVER (ORDER BY start_date) AS rn1
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 rn2
FROM projects
WHERE end_date NOT IN (
SELECT start_date FROM projects
)
)
SELECT
cte1.start_date,
cte2.end_date
FROM cte1
JOIN cte2
ON cte1.rn1 = cte2.rn2
ORDER BY
DATEDIFF(cte2.end_date, cte1.start_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 →
WITH cte1 AS ( SELECT start_date, ROW_NUMBER() OVER (ORDER BY start_date) AS rn1 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 rn2 FROM projects WHERE end_date NOT IN ( SELECT start_date FROM projects ) ) SELECT cte1.start_date, cte2.end_date FROM cte1 JOIN cte2 ON cte1.rn1 = cte2.rn2 ORDER BY DATEDIFF(cte2.end_date, cte1.start_date), cte1.start_date;