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.
This is the easiest solution. We create project groups by subtracting the row number from the start date which essentially give us same date for the same project. And then we group by project grp.
WITH ProjectGroups AS (
SELECT
*,
DATE_SUB(Start_Date, INTERVAL ROW_NUMBER() OVER(ORDER BY Start_Date) DAY) AS grp
FROM Projects
),
GroupedProjects AS (
SELECT
MIN(Start_Date) AS project_start,
MAX(End_Date) AS project_end,
DATEDIFF(MAX(End_Date), MIN(Start_Date)) AS project_days
FROM ProjectGroups
GROUP BY grp
)
SELECT
project_start,
project_end
FROM GroupedProjects
ORDER BY project_days ASC, 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 →
This is the easiest solution. We create project groups by subtracting the row number from the start date which essentially give us same date for the same project. And then we group by project grp.
Task_ID Start_Date End_Date RowNumber Group Identifier (Start_Date - RowNumber) 1 2023-01-01 2023-01-02 1 2023-01-01 - 1 = 2022-12-31 2 2023-01-02 2023-01-03 2 2023-01-02 - 2 = 2022-12-31 3 2023-01-03 2023-01-04 3 2023-01-03 - 3 = 2022-12-31
WITH ProjectGroups AS ( SELECT *, DATE_SUB(Start_Date, INTERVAL ROW_NUMBER() OVER(ORDER BY Start_Date) DAY) AS grp FROM Projects ), GroupedProjects AS ( SELECT MIN(Start_Date) AS project_start, MAX(End_Date) AS project_end, DATEDIFF(MAX(End_Date), MIN(Start_Date)) AS project_days FROM ProjectGroups GROUP BY grp ) SELECT project_start, project_end FROM GroupedProjects ORDER BY project_days ASC, project_start;