[TIL165] solvesql - 세션 유지시간 10분으로 재정의하기(max)

문제 설명

GA(Google Analytics)는 기본적으로 사용자가 일정 시간 동안 아무 행동을 하지 않으면 기존 세션을 종료하고 새로운 세션을 생성한다.

이번 문제에서는 기존 ga_session_id를 그대로 사용하는 것이 아니라,

"사용자가 10분(600초) 이상 행동하지 않으면 새로운 세션을 시작한다"

라는 규칙으로 세션을 다시 정의해야 했다.

최종적으로는 특정 사용자(a8Xu9GO6TB)의 모든 이벤트에 대해 새로운 세션 번호(new_session_id)를 부여해야 한다.

핵심 조건은 다음과 같다.

• 사용자 : a8Xu9GO6TB
• 10분 이상 행동이 없으면 새로운 세션 시작
• 세션 번호는 1부터 시작
• 세션 시작 순서대로 1씩 증가
• 이벤트 발생 시각 순으로 출력

이 문제는 단순 집계 문제가 아니라 사용자 행동 로그를 기반으로 세션을 재정의하는 Sessionization 문제이다.

---

내가 처음 생각한 접근

처음에는 이전 이벤트와 현재 이벤트의 시간 차이를 계산한 뒤,

10분 이상 차이가 나면 새로운 세션

이라고 판단하는 것까지는 쉽게 떠올릴 수 있었다.

그래서 자연스럽게 다음과 같은 흐름을 생각했다.

이전 이벤트 조회
→ 시간 차이 계산
→ 10분 이상 여부 판단
→ 세션 번호 생성

여기까지의 방향은 맞았다.

실제로 이런 유형은 대부분 LAG()와 TIMESTAMPDIFF()를 사용하기 때문에 접근 자체는 크게 틀리지 않았다.

---

내가 헷갈렸던 부분

이번 문제는 사실 시간 차이를 구하는 것이 어려운 문제가 아니었다.

오히려 "새로운 세션 번호를 어떻게 생성할 것인가?" 가 핵심이었다.

처음에는

세션 시작점의 ID를 찾아서
MAX() OVER()로 아래 행들에 계속 전파

하는 방식으로 접근했다.

예를 들어 세션 시작 행이

1
4
7

이라면

전파 후에는

1
1
1
4
4
7
7

형태가 된다.

처음에는 이것이 곧 세션 번호라고 생각했다.

하지만 문제에서 요구하는 것은

1
1
1
2
2
3
3

이었다.

즉,

세션 시작 행 번호와 세션 번호는 서로 다른 개념이었다.

---

또 하나 헷갈렸던 부분은

LAG(event_timestamp_kst, 1)

의 숫자 1이었다.

처음에는 특별한 의미가 있는 줄 알았지만,

실제로는

현재 행 기준 1행 이전 값

이라는 의미였다.

즉,

LAG(col)

와

LAG(col,1)

은 동일하다.

---

최종 해결 방법

이번 문제는 다음 순서로 해결할 수 있었다.

1단계. 이전 이벤트 시각 조회

사용자의 이벤트를 시간순으로 정렬한 뒤 LAG()를 이용해 직전 이벤트 시각을 가져온다.

이 단계의 목적은

"현재 이벤트와 직전 이벤트가 얼마나 떨어져 있는가?"

를 계산하기 위함이다.

---

2단계. 이벤트 간 시간 차이 계산

TIMESTAMPDIFF()를 사용하여

현재 이벤트 시각 - 이전 이벤트 시각

을 초 단위로 계산한다.

이 결과가 last_event 컬럼이다.

---

3단계. 세션 시작점 찾기

다음 조건을 만족하면 새로운 세션 시작으로 간주한다.

• 첫 번째 이벤트
• 이전 이벤트와 600초 이상 차이

이 경우 현재 행의 id를 반환한다.

그 외에는 NULL을 반환한다.

---

4단계. 가장 최근 세션 시작 ID 전파

MAX() OVER()를 이용한다.

이 단계가 이번 문제의 핵심이다.

현재까지 발견된 가장 최근 세션 시작 ID를 아래 행들에 계속 전파한다.

결과적으로

1
NULL
NULL
4
NULL
7

이

1
1
1
4
4
7

형태로 변환된다.

---

5단계. DENSE_RANK()로 세션 번호 생성

전파된 세션 시작 ID는

1
1
1
4
4
7

형태이다.

문제는 이것을

1
1
1
2
2
3

으로 바꾸는 것이다.

이를 위해

DENSE_RANK()

를 사용한다.

DENSE_RANK()는 서로 다른 그룹에 순차적인 번호를 부여하기 때문에

세션 시작 ID를 실제 세션 번호로 변환할 수 있다.

---

정답 쿼리

WITH a AS (
    SELECT user_pseudo_id,event_timestamp_kst,
        TIMESTAMPDIFF(
            SECOND,
            LAG(event_timestamp_kst,1)
            OVER(ORDER BY event_timestamp_kst),
            event_timestamp_kst
        ) AS last_event,
        ROW_NUMBER() OVER(ORDER BY event_timestamp_kst) AS id,
        event_name,
        ga_session_id
    FROM ga
    WHERE user_pseudo_id = 'a8Xu9GO6TB'
    order by event_timestamp_kst
)
SELECT user_pseudo_id,
event_timestamp_kst,
event_name,
ga_session_id,
DENSE_RANK() over(order by new_session_id) as new_session_id
FROM (
    SELECT *,
        MAX(
            CASE
                WHEN last_event IS NULL THEN id
                WHEN last_event >= 600 THEN id END) OVER(ORDER BY event_timestamp_kst) AS new_session_id
    FROM a
    ORDER BY event_timestamp_kst
) f;

---

핵심 SQL 개념 정리

LAG()

이전 행의 값을 조회하는 윈도우 함수이다.

주로 다음과 같은 상황에서 사용한다.

• 이전 주문일 비교
• 이전 로그인 시각 비교
• 이전 측정값 비교
• 사용자 행동 로그 분석

실무에서는 "현재 값과 이전 값 비교" 문제가 나오면 거의 항상 등장한다.

---

TIMESTAMPDIFF()

두 시각 사이의 차이를 계산하는 함수이다.

이번 문제에서는

이전 이벤트 시각
↔ 현재 이벤트 시각

사이의 차이를 계산하는 데 사용되었다.

세션 분석, 사용자 행동 분석, 리텐션 분석에서 자주 사용된다.

---

MAX() OVER()

보통 최댓값을 구하는 함수로 생각하기 쉽다.

하지만 이번 문제에서는

"가장 최근 세션 시작점을 아래로 전파"

하는 역할로 사용되었다.

이런 방식은 Gap & Island 문제에서 자주 등장한다.

---

DENSE_RANK()

중복 없는 순위를 생성하는 윈도우 함수이다.

이번 문제에서는

1
1
1
4
4
7

을

1
1
1
2
2
3

으로 변환하는 역할을 했다.

그룹 번호를 생성하는 문제에서 매우 자주 사용된다.

---

이번 문제에서 배운 패턴

사용자 행동 로그 정렬

↓

이전 이벤트 조회 (LAG)

↓

이벤트 간 시간 차이 계산

↓

세션 시작점 탐지

↓

MAX() OVER()로 시작점 전파

↓

DENSE_RANK()로 그룹 번호 생성

↓

최종 세션 ID 생성

---

한 줄 요약

이번 문제는 이벤트 간 시간 차이로 세션 시작점을 찾고, MAX() OVER()로 시작점을 전파한 뒤 DENSE_RANK()로 연속된 세션 번호를 생성하는 Sessionization(Gap & Island) 패턴을 학습한 문제였다.