--MS SQL solution for beginer WITH cte1 AS ( SELECT start_date, row_number() OVER(ORDER BY start_date) AS rowNumber FROM projects WHERE start_date NOT IN (SELECT end_date FROM projects) ), cte2 AS ( SELECT end_date, row_number() OVER(ORDER BY end_date) AS rowNumber FROM projects WHERE end_date NOT IN (SELECT start_date FROM projects) ) SELECT cte1.start_date, cte2.end_date FROM cte1 INNER JOIN cte2 ON cte1.rowNumber = cte2.rowNumber ORDER BY datediff(day, cte1.start_date, cte2.end_date) , cte1.start_date;

WITH myprojectscte AS( SELECT task_id, start_date, end_date, (start_date) - ROW_NUMBER() OVER (ORDER BY start_date) as grp_id FROM Projects ) SELECT MIN(start_date) AS start_date, MAX(end_date) AS end_date FROM myprojectscte GROUP BY grp_id ORDER BY (MAX(end_date)- MIN(start_date)) ASC, start_date ASC;

WITH PSEQ AS (SELECT Task_ID, Start_Date, End_Date, DATE_SUB(Start_Date,INTERVAL (ROW_NUMBER() OVER (ORDER BY Start_Date)) DAY) AS PGroup FROM Projects)

SELECT
    MIN(Start_Date) AS PStart,
    MAX(End_Date) AS PEnd
FROM
    PSEQ
GROUP BY
    PGroup
ORDER BY COUNT(*) ASC, MIN(Start_Date) ASC

ORACLE:

WITH t1 AS (
    SELECT CONNECT_BY_ROOT(start_date) s_date, end_date e_date, level lvl
    FROM Projects
    WHERE CONNECT_BY_ISLEAF = 1
    CONNECT BY NOCYCLE PRIOR end_date = start_date
),
t2 AS (
    SELECT s_date, e_date,
        ROW_NUMBER() OVER (PARTITION BY e_date ORDER BY lvl DESC) AS rn
    FROM t1
)
SELECT s_date, e_date
FROM t2
WHERE rn = 1
ORDER BY (e_date-s_date), s_date
;

with cte as ( select *,Start_Date - row_number() over(order by End_Date) as rn from Projects ) select min(Start_Date), max(End_Date) from cte group by rn order by max(End_Date)-min(Start_Date), min(Start_Date)