Oracle 18c

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

CREATE TABLE table_name ( user_id, activity_date, foc_id ) AS
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '14:20:34' HOUR TO SECOND, 'A240' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '14:21:23' HOUR TO SECOND, 'A240' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '14:21:23' HOUR TO SECOND, 'A240' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '14:21:23' HOUR TO SECOND, 'A240' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '15:19:39' HOUR TO SECOND, 'B410' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '15:19:44' HOUR TO SECOND, 'B410' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '15:19:58' HOUR TO SECOND, 'B410' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '15:20:11' HOUR TO SECOND, 'B410' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '15:22:16' HOUR TO SECOND, 'A240' FROM DUAL UNION ALL
SELECT 'UVAC3', DATE '2020-11-04' + INTERVAL '15:22:33' HOUR TO SECOND, 'A240' FROM DUAL;
10 rows affected

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT user_id,
       activity_date,
       foc_id,
       ROW_NUMBER() OVER ( PARTITION BY user_id, mno ORDER BY activity_date ) AS seq_num
FROM   table_name
MATCH_RECOGNIZE (
  PARTITION BY user_id
  ORDER     BY activity_date
  MEASURES
    MATCH_NUMBER() AS mno
  ALL ROWS PER MATCH
  PATTERN ( same_foc_id* last_row  )
  DEFINE
    same_foc_id AS FIRST( foc_id ) = NEXT( foc_id )
)


USER_ID
ACTIVITY_DATE
FOC_ID
SEQ_NUM


UVAC3
2020-11-04 14:20:34
A240
1

UVAC3
2020-11-04 14:21:23
A240
2

UVAC3
2020-11-04 14:21:23
A240
3

UVAC3
2020-11-04 14:21:23
A240
4

UVAC3
2020-11-04 15:19:39
B410
1

UVAC3
2020-11-04 15:19:44
B410
2

UVAC3
2020-11-04 15:19:58
B410
3

UVAC3
2020-11-04 15:20:11
B410
4

UVAC3
2020-11-04 15:22:16
A240
1

UVAC3
2020-11-04 15:22:33
A240
2


SELECT user_id,
       activity_date,
       foc_id,
       ROW_NUMBER() OVER ( PARTITION BY user_id, foc_id, grp ORDER BY activity_date ) AS seq_num
FROM   (
  SELECT user_id,
         activity_date,
         foc_id,
         ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY activity_date )
           - ROW_NUMBER() OVER ( PARTITION BY user_id, foc_id ORDER BY activity_date ) AS grp
  FROM   table_name
)
ORDER BY user_id, activity_date


USER_ID
ACTIVITY_DATE
FOC_ID
SEQ_NUM


UVAC3
2020-11-04 14:20:34
A240
1

UVAC3
2020-11-04 14:21:23
A240
2

UVAC3
2020-11-04 14:21:23
A240
3

UVAC3
2020-11-04 14:21:23
A240
4

UVAC3
2020-11-04 15:19:39
B410
1

UVAC3
2020-11-04 15:19:44
B410
2

UVAC3
2020-11-04 15:19:58
B410
3

UVAC3
2020-11-04 15:20:11
B410
4

UVAC3
2020-11-04 15:22:16
A240
1

UVAC3
2020-11-04 15:22:33
A240
2

USER_ID	ACTIVITY_DATE	FOC_ID	SEQ_NUM
UVAC3	2020-11-04 14:20:34	A240	1
UVAC3	2020-11-04 14:21:23	A240	2
UVAC3	2020-11-04 14:21:23	A240	3
UVAC3	2020-11-04 14:21:23	A240	4
UVAC3	2020-11-04 15:19:39	B410	1
UVAC3	2020-11-04 15:19:44	B410	2
UVAC3	2020-11-04 15:19:58	B410	3
UVAC3	2020-11-04 15:20:11	B410	4
UVAC3	2020-11-04 15:22:16	A240	1
UVAC3	2020-11-04 15:22:33	A240	2