SQL to find start and end date for an ID

I have a table with ID, start_date and end_date columns.

Table:

ID    start_date    end_date
1     01/01/2017    01/01/2018
1     01/01/2018    01/01/2019
1     01/01/2019    01/01/2020
2     01/01/2016    01/01/2017
2     01/01/2017    01/01/2018
2     01/01/2019    01/01/2020

I want to write a query to get the following output:

Output:

ID    start_date    end_date
1     01/01/2017    01/01/2020
2     01/01/2016    01/01/2018
2     01/01/2019    01/01/2020

Answer

You can do a cumulative sum to solve this variation of the gaps-and-island problem:

select 
    id,
    min(start_date) start_date,
    max(end_date) end_date
from (
    select 
        t.*,
        sum(case when start_date = lag_end_date then 0 else 1 end) 
            over(partition by id order by start_date) grp
    from (
        select 
            t.*,
            lag(end_date) over(partition by id order by start_date) lag_end_date
        from mytable t
    ) t
) t
group by id, grp
order by id, grp

Demo on DB Fiddle:

ID | START_DATE | END_DATE  
-: | :--------- | :---------
 1 | 01/01/2017 | 01/01/2020
 2 | 01/01/2016 | 01/01/2018
 2 | 01/01/2019 | 01/01/2020