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.
mysql: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 rn1 = 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 →
mysql: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 rn1 = rn2 ORDER BY DATEDIFF(cte2.end_date, cte1.start_date), cte1.start_date;