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
with cte (unique_id, start_date, end_date, start_unique_id) as (
select unique_id, start_date, end_date, unique_id
from t
where not exists (select 1 from t t2 where t.sequential_id = t2.unique_id)
union all
select t.unique_id, t.start_date, t.end_date, cte.start_unique_id
from cte join
t
on cte.unique_id = t.sequential_id
)
select *
from cte
| UNIQUE_ID | START_DATE | END_DATE | START_UNIQUE_ID |
|---|---|---|---|
| 001 | 01-JAN-21 | 15-JAN-21 | 001 |
| 004 | 10-MAR-21 | 11-MAR-21 | 004 |
| 005 | 19-MAR-21 | null | 005 |
| 002 | 01-FEB-21 | 16-FEB-21 | 001 |
| 003 | 01-MAR-21 | 17-MAR-21 | 001 |