Oracle 18c

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, country, REGISTRATION_TS) AS
SELECT '56053', 'CH', DATE '2022-07-05' + INTERVAL '20:57:47' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'CH', DATE '2022-07-05' + INTERVAL '23:26:05' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'CH', DATE '2022-07-06' + INTERVAL '03:40:18' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'CH', DATE '2022-07-06' + INTERVAL '03:42:58' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'DE', DATE '2022-07-06' + INTERVAL '07:50:21' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'DE', DATE '2022-07-12' + INTERVAL '05:05:14' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'DE', DATE '2022-07-13' + INTERVAL '12:43:06' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'CH', DATE '2022-07-26' + INTERVAL '22:52:20' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'CH', DATE '2022-07-27' + INTERVAL '04:05:14' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'DE', DATE '2022-07-27' + INTERVAL '08:47:55' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '56053', 'DE', DATE '2022-07-27' + INTERVAL '15:34:32' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'SI', DATE '2022-07-29' + INTERVAL '18:05:11' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'SI', DATE '2022-07-29' + INTERVAL '18:13:21' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'AT', DATE '2022-07-30' + INTERVAL '07:35:15' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'DE', DATE '2022-07-30' + INTERVAL '07:35:15' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'AT', DATE '2022-07-30' + INTERVAL '07:38:06' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'AT', DATE '2022-07-30' + INTERVAL '07:38:06' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'AT', DATE '2022-07-30' + INTERVAL '07:46:16' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'AT', DATE '2022-07-30' + INTERVAL '07:46:16' HOUR TO SECOND FROM DUAL UNION ALL
SELECT '86EBD', 'SK', DATE '2022-07-30' + INTERVAL '13:14:45' HOUR TO SECOND FROM DUAL;
20 rows affected

SELECT *
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY id
  ORDER BY registration_ts
  MEASURES
    MATCH_NUMBER() AS mno
  ALL ROWS PER MATCH
  PATTERN (same_country+)
  DEFINE same_country AS FIRST(country) = country
)


ID
REGISTRATION_TS
MNO
COUNTRY


56053
2022-07-05 20:57:47
1
CH

56053
2022-07-05 23:26:05
1
CH

56053
2022-07-06 03:40:18
1
CH

56053
2022-07-06 03:42:58
1
CH

56053
2022-07-06 07:50:21
2
DE

56053
2022-07-12 05:05:14
2
DE

56053
2022-07-13 12:43:06
2
DE

56053
2022-07-26 22:52:20
3
CH

56053
2022-07-27 04:05:14
3
CH

56053
2022-07-27 08:47:55
4
DE

56053
2022-07-27 15:34:32
4
DE

86EBD
2022-07-29 18:05:11
1
SI

86EBD
2022-07-29 18:13:21
1
SI

86EBD
2022-07-30 07:35:15
2
AT

86EBD
2022-07-30 07:35:15
3
DE

86EBD
2022-07-30 07:38:06
4
AT

86EBD
2022-07-30 07:38:06
4
AT

86EBD
2022-07-30 07:46:16
4
AT

86EBD
2022-07-30 07:46:16
4
AT

86EBD
2022-07-30 13:14:45
5
SK

ID	REGISTRATION_TS	MNO	COUNTRY
56053	2022-07-05 20:57:47	1	CH
56053	2022-07-05 23:26:05	1	CH
56053	2022-07-06 03:40:18	1	CH
56053	2022-07-06 03:42:58	1	CH
56053	2022-07-06 07:50:21	2	DE
56053	2022-07-12 05:05:14	2	DE
56053	2022-07-13 12:43:06	2	DE
56053	2022-07-26 22:52:20	3	CH
56053	2022-07-27 04:05:14	3	CH
56053	2022-07-27 08:47:55	4	DE
56053	2022-07-27 15:34:32	4	DE
86EBD	2022-07-29 18:05:11	1	SI
86EBD	2022-07-29 18:13:21	1	SI
86EBD	2022-07-30 07:35:15	2	AT
86EBD	2022-07-30 07:35:15	3	DE
86EBD	2022-07-30 07:38:06	4	AT
86EBD	2022-07-30 07:38:06	4	AT
86EBD	2022-07-30 07:46:16	4	AT
86EBD	2022-07-30 07:46:16	4	AT
86EBD	2022-07-30 13:14:45	5	SK