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
An unexpected error occurred. Please try reloading the page. If problem persists, please contact support@hackerrank.com
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;