set @st_date = CURDATE();
with 
f as (
    SELECT Start_Date st,
            (LAG(end_date) over w) lend,
            End_Date
    FROM Projects
    Window w as (ORDER BY Start_Date)
),
f2 as (
    select
        if (f.lend = f.St, @st_date, @st_date := f.St) st,
        f.end_date end_date
    from f
)
select distinct
    f2.st, 
    MAX(f2.end_date) over start_d
from f2
window start_d as (partition by f2.st)
order by (count(*) over start_d), f2.st
;

WITH CTE AS 
(
SELECT
START_DATE
,END_DATE
,CASE 
WHEN ROW_NUMBER() OVER(ORDER BY END_DATE DESC) = 1  THEN END_DATE  
WHEN LEAD(END_DATE) OVER(ORDER BY END_DATE ASC)- END_DATE>1 THEN END_DATE ELSE NULL END AS PROJECT_END_DATE
,CASE WHEN ROW_NUMBER() OVER(ORDER BY END_DATE ASC) = 1 THEN START_DATE 
WHEN START_DATE - LAG(START_DATE) OVER(ORDER BY END_DATE ASC)>1 THEN START_DATE
ELSE NULL END AS PROJECT_START_DATE
FROM PROJECTS
)
,SUBQ AS 
(
SELECT
START_DATE
,END_DATE
,PROJECT_START_DATE
,PROJECT_END_DATE
FROM CTE
)
,CALC AS
(
SELECT 
STRT.PROJECT_START_DATE
,END.PROJECT_END_DATE
,1+END.PROJECT_END_DATE - STRT.PROJECT_START_DATE AS PROJECT_LENGTH
FROM 
(
SELECT 
x.PROJECT_START_DATE
,ROW_NUMBER() OVER(ORDER BY PROJECT_START_DATE ASC) AS RN__
FROM (SELECT * FROM SUBQ WHERE  PROJECT_START_DATE IS NOT NULL) x
)STRT
INNER JOIN 
(
SELECT 
y.PROJECT_END_DATE
,ROW_NUMBER() OVER(ORDER BY PROJECT_END_DATE ASC) AS RN__
FROM (SELECT * FROM SUBQ WHERE  PROJECT_END_DATE IS NOT NULL) y
)END
ON STRT.RN__ = END.RN__
)
SELECT
PROJECT_START_DATE, PROJECT_END_DATE
FROM CALC
ORDER BY PROJECT_LENGTH ASC, PROJECT_START_DATE ASC;

-- Oracle
SELECT 
    a.start_date,
    MIN(b.end_date) AS end_date
FROM 
    (SELECT start_date FROM projects 
     WHERE start_date NOT IN (SELECT end_date FROM projects)) a,
    (SELECT end_date FROM projects 
     WHERE end_date NOT IN (SELECT start_date FROM projects)) b
WHERE 
    a.start_date < b.end_date
GROUP BY 
    a.start_date
ORDER BY 
    min(b.end_date) - a.start_date, a.start_date;

WITH
cte AS( SELECT
            COALESCE(LAG(start_date) OVER(ORDER BY start_date), '1900-01-01') AS prev_date,
            start_date, end_date,
            COALESCE(LEAD(end_date) OVER(ORDER BY end_date), '2100-01-01') AS next_date
        FROM projects),
        
cte2 AS ( SELECT start_date, ROW_NUMBER() OVER(ORDER BY start_date) AS rn
        FROM cte 
        WHERE ABS(DATEDIFF(DAY, start_date, prev_date)) <> 1),
        
cte3 AS ( SELECT end_date , ROW_NUMBER() OVER(ORDER BY end_date) AS rn
        FROM cte 
        WHERE ABS(DATEDIFF(DAY, next_date, end_date)) <> 1)

SELECT cte2.start_date, cte3.end_date
FROM cte2
JOIN cte3 ON cte2.rn = cte3.rn
ORDER BY ABS(DATEDIFF(DAY, cte2.start_date, cte3.end_date)), cte2.start_date;

WITH tasks_with_flag AS (
  SELECT *,
         LAG(End_Date) OVER (ORDER BY Start_Date) as prev_end
  FROM Projects
),
tasks_with_project AS (
  SELECT *,
         SUM(CASE WHEN prev_end IS NULL OR Start_Date != prev_end THEN 1 ELSE 0 END)
             OVER (ORDER BY Start_Date) AS project_id
  FROM tasks_with_flag
)

  SELECT
    MIN(Start_Date) as project_start,
    MAX(End_Date) as project_end
  FROM tasks_with_project
  GROUP BY project_id
  ORDER BY DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)), project_start