WITH
  partitioned AS
  (
    SELECT
      *
    , DATEDIFF(start_Date, '1970-01-01') - ROW_NUMBER() OVER (ORDER BY start_Date ASC) AS PartID
    FROM
      Projects
  )
SELECT
  MIN(start_Date) AS StartDate
, MAX(end_date) AS EndDate
FROM
  partitioned
GROUP BY
  PartID

ORDER BY
  COUNT(*) ASC , 
  StartDate ASC

/*
The difference between end and start date is equal to 1 day for each row 

If end date tasks are consecutive, then they are in the same project 

Interested in the total number of different projects completed 

Output start and end dates of projects listed by number of days it took to complete the project in ASC order. 

If Conscutive by one day, its another day added to the project

IF more than one project has the same number of completion date, order by start of the project 
*/

WITH CTE AS(
SELECT 
    Start_Date,
    End_Date,
    DATEDIFF(day,ROW_NUMBER() OVER (ORDER BY Start_Date ASC), Start_Date) AS Project_Identifyer
FROM 
    Projects
)

SELECT 
    MIN(Start_Date),
    MAX(End_Date)
FROM 
    CTE
GROUP BY 
    Project_Identifyer
ORDER BY 
    DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)) ASC, MIN(Start_Date);

with first_date as (
    select p.start_date, p.end_date 
    from projects as p
    left join projects as pp on p.start_date = pp.end_date
    where pp.end_date is null
    
    union all
    
    select fd.start_date, ppp.end_date
    from first_date as fd
    join projects as ppp on fd.end_date = ppp.start_date
)
select start_date, max(end_date) 
from first_date
group by start_date
order by count(end_date), start_date;