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 NumberedTasks as (
Select *, row_number() over (order by Task_ID) as rn
from Projects
),
grouptask as (
select *,
date_sub(Start_Date, interval rn day) as grp
from
)
SELECT
MIN(Start_Date) AS Start_Date,
MAX(End_Date) AS End_Date,
DATEDIFF(MAX(End_Date), MIN(Start_Date)) + 1 AS Project_Duration
FROM GroupedTasks
GROUP BY grp
ORDER BY Project_Duration, 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 NumberedTasks as ( Select *, row_number() over (order by Task_ID) as rn from Projects ),
grouptask as ( select *, date_sub(Start_Date, interval rn day) as grp from ) SELECT MIN(Start_Date) AS Start_Date, MAX(End_Date) AS End_Date, DATEDIFF(MAX(End_Date), MIN(Start_Date)) + 1 AS Project_Duration FROM GroupedTasks GROUP BY grp ORDER BY Project_Duration, Start_Date;