Used Oracle:

WITH proj_prev_dates AS 
(
    SELECT Task_ID, Start_Date, End_Date, 
        LAG(End_Date) OVER(ORDER BY Start_Date) AS prev_date
    FROM Projects
    ),
proj_flags AS
(
    SELECT 
        ppd.*,
        CASE 
            WHEN prev_date IS NULL THEN 1
            WHEN prev_date = Start_Date THEN 0 
            ELSE 1
        END as proj_flag  
    FROM proj_prev_dates ppd
    ),
proj_labelled AS 
(
    SELECT pf.*,
        SUM(proj_flag) OVER(ORDER BY Start_Date) AS proj_ID
    FROM proj_flags pf  
    )
SELECT min_date, max_date
FROM
(   SELECT 
        proj_ID, 
        MIN(Start_Date) AS min_date, 
        MAX(End_Date) AS max_date,
        MAX(End_Date) - MIN(Start_Date) AS  time_taken
    FROM proj_labelled
    GROUP BY proj_ID
    )
ORDER BY time_taken, min_date;

/* Enter your query here. */ with cte as (select start_date, end_date, - row_number() over (order by end_date) as date_lag, end_date - row_number() over (order by end_date) as date_lag_2 from projects order by start_date asc)

, cte2 as ( select min(start_date) min_date, max(end_date) max_date, count(*) total_days from cte group by date_lag_2 )

select min_date, max_date from cte2 order by total_days asc, min_date asc

`with all_date as ( select Start_Date as date_ from Projects union all select End_Date as date_ from Projects ) , table_data_point as ( select date_, count(date_) as number_dup from all_date group by 1 having count(date_) = 1 ) , table_add_key_point as ( select date_, case when date_ in (select Start_Date as date_ from Projects) then 'Start' else 'End' end as Segment from table_data_point ) , start_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'Start' ) , end_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'End' ) select start_table.date_ as start_date, end_table.date_ as end_date from start_table join end_table using(rk) order by datediff(end_table.date_, start_table.date_) asc, 1 asc

I solved this problem by grouping consecutive tasks based on their start dates. First, I created a CTE called NumberedTasks where I assigned a row number (ROW_NUMBER() OVER (ORDER BY Start_Date)) to each task ordered by the Start_Date. This row number gave me a way to track the original sequence of tasks.

Next, I created another CTE called GroupedTasks. In this step, I calculated a GroupID for each task by subtracting the row number from the task’s start date. This approach forces all consecutive dates (without gaps) to align with the same GroupID, because when tasks happen on consecutive days, this difference remains constant.

Finally, I grouped by this GroupID and selected the minimum start date and the maximum end date within each group. This gave me the start and end date of each project.

For sorting, I first ordered the output by the number of days the project took (DATEDIFF between start and end dates), and for projects with the same duration, I sorted them by their start date.

This approach was purely window function based, and I didn’t use any unnecessary joins or variables.

WITH NumberedTasks AS (
    SELECT
        Task_ID,
        Start_Date,
        End_Date,
        ROW_NUMBER() OVER (ORDER BY Start_Date) AS RowNum
    FROM Projects
),
GroupedTasks AS (
    SELECT
        Task_ID,
        Start_Date,
        End_Date,
        DATEADD(DAY, -ROW_NUMBER() OVER (ORDER BY Start_Date), Start_Date) AS GroupID
    FROM Projects
)
SELECT
    MIN(Start_Date) AS Start_Date,
    MAX(End_Date) AS End_Date
FROM GroupedTasks
GROUP BY GroupID
ORDER BY
    DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)),
    MIN(Start_Date);

MySQL:

WITH table AS (SELECT *, End_Date - ROW_NUMBER() OVER (ORDER BY End_Date) AS gr FROM Projects)

SELECT MIN(Start_Date) AS Start_Date, MAX(End_Date) AS End_Date FROM table1 GROUP BY gr ORDER BY COUNT(*), MIN(Start_Date) ;