Oracle 21c

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

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE table_name (id, start_date, end_date) AS
  SELECT   12345, DATE '2015-08-07', DATE '2065-08-07' FROM DUAL UNION ALL
  SELECT   12345, DATE '2015-08-22', DATE '2016-01-01' FROM DUAL UNION ALL
  SELECT   12345, DATE '2016-03-24', DATE '2066-03-23' FROM DUAL UNION ALL
  SELECT   12345, DATE '2016-07-06', DATE '2017-12-31' FROM DUAL UNION ALL
  SELECT   12345, DATE '2016-12-31', DATE '2041-12-31' FROM DUAL UNION ALL
  SELECT 4662828, DATE '2015-08-22', DATE '2015-12-22' FROM DUAL UNION ALL
  SELECT 4662828, DATE '2016-01-01', DATE '2018-08-01' FROM DUAL UNION ALL
  SELECT 4662828, DATE '2017-06-10', DATE '2018-12-31' FROM DUAL UNION ALL
  SELECT 4662828, DATE '2018-12-01', DATE '2072-12-04' FROM DUAL;
9 rows affected

SELECT *
FROM   table_name
       MATCH_RECOGNIZE(
         PARTITION BY id
         ORDER BY start_date, end_date
         MEASURES
           FIRST(start_date) AS start_date,
           MAX(end_date) AS end_date
         PATTERN (overlapping* final_range)
         DEFINE overlapping AS NEXT(start_date) <= MAX(end_date)
       )


ID
START_DATE
END_DATE


12345
2015-08-07 00:00:00
2066-03-23 00:00:00

4662828
2015-08-22 00:00:00
2015-12-22 00:00:00

4662828
2016-01-01 00:00:00
2072-12-04 00:00:00


SELECT id,
       MIN(dt) AS start_date,
       MAX(dt) AS end_date
FROM   (
  SELECT id,
         dt,
         SUM(group_change) OVER (PARTITION BY id ORDER BY dt)
          AS grp
  FROM   (
    SELECT id,
           dt,
           CASE type * SUM(type) OVER (PARTITION BY id ORDER BY dt, type DESC, ROWNUM)
           WHEN 1
           THEN 1
           ELSE 0
           END AS group_change
    FROM   table_name
           UNPIVOT (dt FOR type IN (start_date AS +1, end_date AS -1))
  )
)
GROUP BY id, grp


ID
START_DATE
END_DATE


12345
2015-08-07 00:00:00
2066-03-23 00:00:00

4662828
2015-08-22 00:00:00
2015-12-22 00:00:00

4662828
2016-01-01 00:00:00
2072-12-04 00:00:00

ID	START_DATE	END_DATE
12345	2015-08-07 00:00:00	2066-03-23 00:00:00
4662828	2015-08-22 00:00:00	2015-12-22 00:00:00
4662828	2016-01-01 00:00:00	2072-12-04 00:00:00