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 ordered_tasks AS (
SELECT
Task_ID,
Start_Date,
End_Date,
ROW_NUMBER() OVER (ORDER BY Start_Date) AS rn
FROM Projects
),
grouped_tasks AS (
SELECT
*,
DATE_SUB(Start_Date, INTERVAL rn DAY) AS grp
FROM ordered_tasks
),
project_ranges AS (
SELECT
MIN(Start_Date) AS project_start,
MAX(End_Date) AS project_end,
DATEDIFF(MAX(End_Date), MIN(Start_Date)) AS duration
FROM grouped_tasks
GROUP BY grp
)
SELECT project_start, project_end
FROM project_ranges
ORDER BY duration, project_start;
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 ordered_tasks AS ( SELECT Task_ID, Start_Date, End_Date, ROW_NUMBER() OVER (ORDER BY Start_Date) AS rn FROM Projects ), grouped_tasks AS ( SELECT *, DATE_SUB(Start_Date, INTERVAL rn DAY) AS grp FROM ordered_tasks ), project_ranges AS ( SELECT MIN(Start_Date) AS project_start, MAX(End_Date) AS project_end, DATEDIFF(MAX(End_Date), MIN(Start_Date)) AS duration FROM grouped_tasks GROUP BY grp ) SELECT project_start, project_end FROM project_ranges ORDER BY duration, project_start;