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 (StDt, EdDt, User_Stat) AS
SELECT DATE '2021-12-20', DATE '2022-06-12', 'A' FROM DUAL UNION ALL
SELECT DATE '2022-06-16', DATE '4712-12-31', 'A' FROM DUAL UNION ALL
SELECT DATE '2022-06-09', DATE '2022-06-30', 'B' FROM DUAL UNION ALL
SELECT DATE '2022-06-09', DATE '2022-06-30', 'C' FROM DUAL UNION ALL
SELECT DATE '2022-06-15', DATE '2022-06-20', 'C' FROM DUAL UNION ALL
SELECT DATE '2022-06-15', DATE '2022-06-20', 'D' FROM DUAL UNION ALL
SELECT DATE '2022-06-18', DATE '2022-06-23', 'D' FROM DUAL UNION ALL
SELECT DATE '2022-06-25', DATE '2022-06-30', 'D' FROM DUAL;
8 rows affected

  SELECT m.*,
         rn
  FROM   table_name
  MATCH_RECOGNIZE(
    PARTITION BY user_stat
    ORDER BY StDt
    MEASURES
      COUNT(*) AS rn,
      MATCH_NUMBER() AS mno
    ALL ROWS PER MATCH
    PATTERN (overlapping* final_row)
    DEFINE
      overlapping AS MAX(eddt) >= NEXT(stdt)
  ) m


USER_STAT
STDT
RN
MNO
EDDT
RN


A
2021-12-20 00:00:00
1
1
2022-06-12 00:00:00
1

A
2022-06-16 00:00:00
1
2
4712-12-31 00:00:00
1

B
2022-06-09 00:00:00
1
1
2022-06-30 00:00:00
1

C
2022-06-09 00:00:00
1
1
2022-06-30 00:00:00
1

C
2022-06-15 00:00:00
2
1
2022-06-20 00:00:00
2

D
2022-06-15 00:00:00
1
1
2022-06-20 00:00:00
1

D
2022-06-18 00:00:00
2
1
2022-06-23 00:00:00
2

D
2022-06-25 00:00:00
1
2
2022-06-30 00:00:00
1


MERGE INTO table_name dst
USING (
  SELECT ROWID AS rid,
         rn,
         MAX(eddt) OVER (PARTITION BY user_stat, mno) AS eddt
  FROM   table_name
  MATCH_RECOGNIZE(
    PARTITION BY user_stat
    ORDER BY StDt
    MEASURES
      COUNT(*) AS rn,
      MATCH_NUMBER() AS mno
    ALL ROWS PER MATCH
    PATTERN (overlapping* final_row)
    DEFINE
      overlapping AS MAX(eddt) >= NEXT(stdt)
  )
) src
ON (dst.ROWID = src.rid)
WHEN MATCHED THEN
  UPDATE
  SET   eddt = src.eddt
  DELETE WHERE rn > 1;
8 rows affected

SELECT * FROM table_name;


STDT
EDDT
USER_STAT


2021-12-20 00:00:00
2022-06-12 00:00:00
A

2022-06-16 00:00:00
4712-12-31 00:00:00
A

2022-06-09 00:00:00
2022-06-30 00:00:00
B

2022-06-09 00:00:00
2022-06-30 00:00:00
C

2022-06-15 00:00:00
2022-06-23 00:00:00
D

2022-06-25 00:00:00
2022-06-30 00:00:00
D

USER_STAT	STDT	RN	MNO	EDDT	RN
A	2021-12-20 00:00:00	1	1	2022-06-12 00:00:00	1
A	2022-06-16 00:00:00	1	2	4712-12-31 00:00:00	1
B	2022-06-09 00:00:00	1	1	2022-06-30 00:00:00	1
C	2022-06-09 00:00:00	1	1	2022-06-30 00:00:00	1
C	2022-06-15 00:00:00	2	1	2022-06-20 00:00:00	2
D	2022-06-15 00:00:00	1	1	2022-06-20 00:00:00	1
D	2022-06-18 00:00:00	2	1	2022-06-23 00:00:00	2
D	2022-06-25 00:00:00	1	2	2022-06-30 00:00:00	1