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.
Asuming that it is one day for each task, the solution proposed of substracting row numbers to date works perfectly. I'll give you one that works when we asume tasks can take more than a day:
SELECT min(Table2.Start_Date) Start_Date, max(Table2.End_Date) End_Date
FROM(SELECT Table1., SUM(Validador) over (partition by 1 order by Start_Date) Project
FROM (SELECT Projects., lag(End_Date) over (partition by 1 order by End_Date) Next_Date,
CASE WHEN Start_Date = lag(End_Date) over (partition by 1 order by End_Date) THEN 0 ELSE 1 END Validador
FROM Projects) Table1) Table2
GROUP BY Table2.Project
ORDER BY count(Table2.Project) ASC, min(Table2.Start_Date) ASC;
We could use with blocks, but i was a little lazy to do it :P.
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 →
Asuming that it is one day for each task, the solution proposed of substracting row numbers to date works perfectly. I'll give you one that works when we asume tasks can take more than a day: SELECT min(Table2.Start_Date) Start_Date, max(Table2.End_Date) End_Date FROM(SELECT Table1., SUM(Validador) over (partition by 1 order by Start_Date) Project FROM (SELECT Projects., lag(End_Date) over (partition by 1 order by End_Date) Next_Date, CASE WHEN Start_Date = lag(End_Date) over (partition by 1 order by End_Date) THEN 0 ELSE 1 END Validador FROM Projects) Table1) Table2 GROUP BY Table2.Project ORDER BY count(Table2.Project) ASC, min(Table2.Start_Date) ASC; We could use with blocks, but i was a little lazy to do it :P.