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 LaggedProjects AS (
SELECT
Task_ID,
Start_Date,
End_Date,
LAG(End_Date) OVER (ORDER BY End_Date) AS Prev_End_Date
FROM Projects
),
ProjectGroups AS (
SELECT
Task_ID,
Start_Date,
End_Date,
SUM(CASE
WHEN Prev_End_Date = Start_Date THEN 0
ELSE 1
END) OVER (ORDER BY End_Date) AS Project_ID
FROM LaggedProjects
),
ProjectDurations AS (
SELECT
Project_ID,
MIN(Start_Date) AS Project_Start,
MAX(End_Date) AS Project_End,
DATEDIFF(day, MIN(Start_Date),MAX(End_Date)) AS Duration
FROM ProjectGroups
GROUP BY Project_ID
)
SELECT Project_Start, Project_End
FROM ProjectDurations
ORDER BY Duration ASC, Project_Start 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 →
WITH LaggedProjects AS ( SELECT Task_ID, Start_Date, End_Date, LAG(End_Date) OVER (ORDER BY End_Date) AS Prev_End_Date FROM Projects ), ProjectGroups AS ( SELECT Task_ID, Start_Date, End_Date, SUM(CASE WHEN Prev_End_Date = Start_Date THEN 0 ELSE 1 END) OVER (ORDER BY End_Date) AS Project_ID FROM LaggedProjects ), ProjectDurations AS ( SELECT Project_ID, MIN(Start_Date) AS Project_Start, MAX(End_Date) AS Project_End, DATEDIFF(day, MIN(Start_Date),MAX(End_Date)) AS Duration FROM ProjectGroups GROUP BY Project_ID ) SELECT Project_Start, Project_End FROM ProjectDurations ORDER BY Duration ASC, Project_Start ASC;