Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t as

select '001' as unique_id, date '2021-01-01' as start_date, date '2021-01-15' as end_date, null as sequential_id from dual union all

select '002' as unique_id, date '2021-02-01' as start_date, date '2021-02-16' as end_date, '001' as sequential_id from dual union all

select '003' as unique_id, date '2021-03-01' as start_date, date '2021-03-17' as end_date, '002' from dual union all

select '004' as unique_id, date '2021-03-10' as start_date, date '2021-03-11' as end_date, null as sequential_id from dual union all

select '005' as unique_id, date '2021-03-19' as start_date, null, null as sequential_id from dual

5 rows affected

select *

from t

match_recognize(

measures

first(unique_id) start_unique_id,

first(start_date) start_date,

last(end_date) end_date

pattern (strt nxt*)

define nxt as sequential_id=prev(unique_id)

);