WITH 방문이력 AS (
    SELECT '1' SEQ, '2024001' GAME_NO, 'USER_A' USER_ID FROM DUAL
    UNION ALL SELECT  '2' SEQ, '2024002' GAME_NO,'USER_A' FROM DUAL 
    UNION ALL SELECT  '3' SEQ, '2024003' GAME_NO,'USER_A' FROM DUAL 
    UNION ALL SELECT  '4' SEQ, '2024004' GAME_NO,'USER_A' FROM DUAL
    UNION ALL SELECT  '5' SEQ, '2024001' GAME_NO,'USER_B' FROM DUAL
    UNION ALL SELECT  '6' SEQ, '2024003' GAME_NO,'USER_B' FROM DUAL
    UNION ALL SELECT  '7' SEQ, '2024004' GAME_NO,'USER_B' FROM DUAL
    UNION ALL SELECT  '8' SEQ, '2024003' GAME_NO,'USER_C' FROM DUAL
) 
SELECT USER_ID, COUNT(CASE WHEN X =1 THEN 1 END)
FROM(SELECT USER_ID, GAME_NO
     , CASE WHEN GAME_NO+1 = LEAD(GAME_NO)OVER(ORDER BY USER_ID, GAME_NO ) THEN 1 
            WHEN GAME_NO-1 = LAG(GAME_NO)OVER(ORDER BY USER_ID, GAME_NO ) THEN 1 ELSE 0 END X
       FROM 방문이력 ) A
    GROUP BY USER_ID; 

연속된 날짜 구하려고 윈도우 함수 사용했습니다.

질문의 의도가 마지막경기(4)를 기준으로 연속 방문한 기록인가요?
1,2,3 번 경기 연속 방문 후 4번 경기 방문 안했으면 0 이 되는 건지?

WITH game AS
(
SELECT 2024001 game_no, 'A팀 vs B팀' game_name, '2024-01-03' game_date
UNION ALL SELECT 2024002, 'A팀 vs C팀', '2024-01-06'
UNION ALL SELECT 2024003, 'A팀 vs D팀', '2024-01-09'
UNION ALL SELECT 2024004, 'A팀 vs E팀', '2024-01-16'
)
, visit AS
(
SELECT 1 seq, 2024001 game_no, 'USER_A' user_id
UNION ALL SELECT 2, 2024002, 'USER_A'
UNION ALL SELECT 3, 2024003, 'USER_A'
UNION ALL SELECT 4, 2024004, 'USER_A'
UNION ALL SELECT 5, 2024001, 'USER_B'
UNION ALL SELECT 6, 2024003, 'USER_B'
UNION ALL SELECT 7, 2024004, 'USER_B'
UNION ALL SELECT 8, 2024003, 'USER_C'
)
SELECT a.user_id
     , COUNT(b.rn) visit_cnt
  FROM (SELECT user_id
             , game_no
             , ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY game_no DESC) rn
          FROM visit
        ) a
  LEFT OUTER JOIN
       (SELECT game_no
             , ROW_NUMBER() OVER(ORDER BY game_no DESC) rn
          FROM game
        ) b
    ON a.game_no = b.game_no
   AND a.rn      = b.rn
 GROUP BY a.user_id
;