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 CTE
AS(
SELECT
*,
COALESCE(LAG(End_Date,1) OVER(ORDER BY Start_Date),Start_Date) AS PED
FROM Projects
),
CTE1
AS(
SELECT
Start_Date,
End_Date,
SUM(CASE WHEN Start_Date = PED THEN 0 ELSE 1 END) OVER(ORDER BY Start_Date) AS grouping
FROM CTE
),
CTE2
AS(
SELECT
*,
COUNT(grouping) OVER(PARTITION BY grouping) AS cnt
FROM CTE1
)
SELECT
MIN(Start_Date) As Proj_Start_Date,
MAX(End_Date) AS Proj_End_Date
FROM CTE2
GROUP BY grouping,cnt
ORDER BY cnt ASC, Proj_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 CTE AS( SELECT *, COALESCE(LAG(End_Date,1) OVER(ORDER BY Start_Date),Start_Date) AS PED FROM Projects ), CTE1 AS( SELECT Start_Date, End_Date, SUM(CASE WHEN Start_Date = PED THEN 0 ELSE 1 END) OVER(ORDER BY Start_Date) AS grouping FROM CTE ), CTE2 AS( SELECT
*, COUNT(grouping) OVER(PARTITION BY grouping) AS cnt FROM CTE1 ) SELECT MIN(Start_Date) As Proj_Start_Date, MAX(End_Date) AS Proj_End_Date FROM CTE2 GROUP BY grouping,cnt ORDER BY cnt ASC, Proj_Start_Date