[SQL 코테] 연속 날짜 그룹핑 패턴 완전 정복 🔥

📝 문제: 연속 접속 일수 구하기

테이블 구조

logins 테이블
login_id | user_id | login_date
---------|---------|------------
1        | 101     | 2024-01-01
2        | 101     | 2024-01-02
3        | 101     | 2024-01-03
4        | 101     | 2024-01-05
5        | 101     | 2024-01-06
6        | 102     | 2024-01-01
7        | 102     | 2024-01-03

요구사항

• 각 유저별로 연속 접속 구간을 찾고, 각 구간의 연속 일수 구하기
• 결과 컬럼: user_id, start_date, end_date, consecutive_days

🧠 핵심 아이디어: 왜 이게 되는 거야?

Step 1: 데이터에 순번 붙이기

user_id = 101 데이터로 보자:

login_date | rn (순번)
-----------|----------
2024-01-01 | 1
2024-01-02 | 2
2024-01-03 | 3
2024-01-05 | 4   ← 날짜는 2일 점프, 순번은 1씩 증가
2024-01-06 | 5

Step 2: (날짜 - 순번) 계산하면 마법이 일어남!

login_date | rn | login_date - rn일
-----------|----|-----------------------
2024-01-01 | 1  | 2023-12-31  ←┐
2024-01-02 | 2  | 2023-12-31  ←┼─ 연속! 같은 값!
2024-01-03 | 3  | 2023-12-31  ←┘
2024-01-05 | 4  | 2024-01-01  ←┐ 
2024-01-06 | 5  | 2024-01-01  ←┴─ 연속! 같은 값!

왜 이렇게 되냐면:

• 연속된 날짜: 날짜가 1씩 증가, 순번도 1씩 증가 → 차이가 일정!
• 끊긴 날짜: 날짜는 2 이상 증가, 순번은 1만 증가 → 차이가 달라짐!

Step 3: 이 값으로 GROUP BY 하면 연속 구간끼리 묶임!

grp = 2023-12-31 그룹: 01-01, 01-02, 01-03 → 연속 3일
grp = 2024-01-01 그룹: 01-05, 01-06 → 연속 2일

✅ 정답 쿼리

WITH 
rn_cte AS ( 
  SELECT 
    user_id, login_date, 
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS rn 
  FROM logins 
), 
grp_cte AS ( 
  SELECT 
    user_id, login_date, 
    DATE_SUB(login_date, INTERVAL rn DAY) AS grp 
  FROM rn_cte 
)
SELECT 
  user_id, 
  MIN(login_date) AS start_date, 
  MAX(login_date) AS end_date, 
  COUNT(*) AS consecutive_days 
FROM grp_cte
GROUP BY user_id, grp 
ORDER BY user_id, start_date;

결과

user_id | start_date | end_date   | consecutive_days
--------|------------|------------|------------------
101     | 2024-01-01 | 2024-01-03 | 3
101     | 2024-01-05 | 2024-01-06 | 2
102     | 2024-01-01 | 2024-01-01 | 1
102     | 2024-01-03 | 2024-01-03 | 1

💡 핵심 포인트

1️⃣ DATE_SUB 문법

DATE_SUB(날짜, INTERVAL 숫자 단위)

DATE_SUB('2024-01-05', INTERVAL 3 DAY)   -- 3일 빼기 → '2024-01-02'
DATE_SUB('2024-01-05', INTERVAL 1 MONTH) -- 1달 빼기 → '2023-12-05'

2️⃣ CTE 여러 개 쓸 때 문법

-- ❌ WITH 두 번 쓰면 안 돼
WITH rn_cte AS (...),
WITH grp_cte AS (...)

-- ✅ WITH 한 번, 쉼표로 연결
WITH 
  rn_cte AS (...),
  grp_cte AS (...)
SELECT ...

3️⃣ 왜 CTE가 2개 필요해?

같은 SELECT에서 방금 만든 별칭 참조 불가!

-- ❌ 안 됨
SELECT 
  ROW_NUMBER() OVER(...) AS rn,
  DATE_SUB(login_date, INTERVAL rn DAY) AS grp  -- rn 못 씀!

해결: CTE 분리해서 먼저 rn 만들고, 다음 CTE에서 참조

4️⃣ GROUP BY가 핵심인 이유

GROUP BY user_id, grp

• 같은 grp = 연속된 날짜들
• MIN(login_date) = 연속 구간 시작일
• MAX(login_date) = 연속 구간 종료일
• COUNT(*) = 연속 일수

🔄 CTE 체인 흐름 정리

logins (원본)
    ↓
rn_cte (순번 추가)
    - user_id, login_date, rn
    ↓
grp_cte (그룹키 추가)  
    - user_id, login_date, grp
    ↓
메인 쿼리 (집계)
    - GROUP BY user_id, grp
    - MIN, MAX, COUNT

🎯 이 패턴 외워두기!

"연속 날짜 그룹핑 = DATE_SUB(날짜, INTERVAL ROW_NUMBER DAY)"

이 패턴은 아래 문제에서 자주 나와:

• 연속 출석 일수
• 연속 구매 일수
• 연속 로그인 보상
• 스트릭(streak) 계산

🎯 라이브 코테 체크리스트

• ROW_NUMBER()는 빈 괄호 — 인자 없음
• DATE_SUB에 INTERVAL n DAY 형식
• CTE 여러 개 = WITH 한 번 + 쉼표
• 같은 SELECT에서 별칭 참조 불가 → CTE 분리

채널톡 DA 인턴 코테 준비 중 정리한 내용입니다 🚀