Oracle 18c

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

CREATE TABLE table_name (SPLIT, PART, DT) AS
SELECT 'S1', 'P1', DATE '2022-09-21' FROM DUAL UNION ALL
SELECT 'S1', 'P2', DATE '2022-09-20' FROM DUAL UNION ALL
SELECT 'S1', 'P1', DATE '2022-09-19' FROM DUAL UNION ALL
SELECT 'S2', 'P1', DATE '2022-09-21' FROM DUAL UNION ALL
SELECT 'S2', 'P2', DATE '2022-09-19' FROM DUAL UNION ALL
SELECT 'S2', 'P1', DATE '2022-09-19' FROM DUAL;
6 rows affected

SELECT t.*,
       CASE
       WHEN part = 'P1'
       AND  LAG(part) OVER (PARTITION BY split ORDER BY dt) = 'P2'
       AND  LAG(dt) OVER (PARTITION BY split ORDER BY dt) = dt - 1
       THEN 'X'
       END AS output
FROM   table_name t


SPLIT
PART
DT
OUTPUT


S1
P1
19-SEP-22
null

S1
P2
20-SEP-22
null

S1
P1
21-SEP-22
X

S2
P1
19-SEP-22
null

S2
P2
19-SEP-22
null

S2
P1
21-SEP-22
null


SELECT split,
       part,
       dt,
       CASE cls WHEN 'P1' THEN 'X' END AS output
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY split
  ORDER BY dt DESC
  MEASURES
    CLASSIFIER() AS cls
  ALL ROWS PER MATCH
  PATTERN (p1 p2 | any_row)
  DEFINE
    p1 AS part = 'P1',
    p2 AS part = 'P2' AND dt + 1 = PREV(dt)
)


SPLIT
PART
DT
OUTPUT


S1
P1
21-SEP-22
X

S1
P2
20-SEP-22
null

S1
P1
19-SEP-22
null

S2
P1
21-SEP-22
null

S2
P2
19-SEP-22
null

S2
P1
19-SEP-22
null

SPLIT	PART	DT	OUTPUT
S1	P1	19-SEP-22	null
S1	P2	20-SEP-22	null
S1	P1	21-SEP-22	X
S2	P1	19-SEP-22	null
S2	P2	19-SEP-22	null
S2	P1	21-SEP-22	null