DAY 10 / [solvesql] Advent of SQL 2024 (세션 유지 시간을 10분으로 재정의하기)

세션 유지 시간을 10분으로 재정의하기

-- 세션을 종료하는 기준을 사용자가 10분 이상 행동하지 않을 때로 수정하여,
-- 사용자 'a8Xu9GO6TB’의 세션을 재정의해 세션 ID를 계산하는 쿼리 작성

WITH filtered AS (
  SELECT event_timestamp_kst
       , TIMEDIFF(event_timestamp_kst, LAG(event_timestamp_kst, 1) OVER(ORDER BY event_timestamp_kst)) AS diff_prev
       , user_pseudo_id
       , event_name
       , ga_session_id
  FROM ga
  WHERE user_pseudo_id = 'a8Xu9GO6TB'
  ORDER BY event_timestamp_kst
  )
, new_session_start AS (
  SELECT *
       , CASE
           WHEN diff_prev IS NULL THEN 1
           WHEN diff_prev >= '00:10:00' THEN 1
           ELSE 0 END AS new_session
  FROM filtered
  )

SELECT user_pseudo_id
     , event_timestamp_kst
     , event_name
     , ga_session_id
     , SUM(new_session) OVER(ORDER BY event_timestamp_kst) AS new_session_id
FROM new_session_start
ORDER BY event_timestamp_kst

 

1번째 시도

=> 테스트 케이스 실패 (이벤트 발생 시각이 동일할 때 이벤트의 우선 순위가 정답 케이스와 다름)

 

2번째 시도

=> 성공 (WITH절에서 윈도우 함수 LAG을 사용할 때 OVER안에 PARTITION BY user_pseudo_id 부분을 제거함)

 

 

정답 쿼리 작성 순서

1. filtered 테이블 만들기 (CTE)

(1) 문제가 요구한 대로 user_pseudo_id = 'a8Xu9GO6TB' 인 경우만 필터링

(2)  '현재 이벤트의 발생 시각'과 '이전 이벤트의 발생 시각'의 차이를 나타내는 컬럼(diff_prev) 생성

-- 윈도우 함수 LAG()을 이용해서 이전 행의 데이터를 가져온 후

-- TIMEDIFF() 함수로 차이를 구함.

 

2. new_session_start 테이블 만들기 (CTE)

(1) filtered 테이블에서  CASE구문으로 아래 조건에 따라 flag 설정

-- '현재 이벤트의 발생 시각'과 '이전 이벤트의 발생 시각'의 차이가 10분 이상인 경우에 1로 표시

-- 처음 발생한 이벤트는 이전 이벤트가 없으므로 시간 차이를 나타낸 컬럼(diff_prev)에 값이 없기 때문에 이 경우도 1로 표시

-- 나머지는 0으로 표시

 

3. flag 표시한 컬럼을 기준으로 누적합을 구하면 new_session_id에 이벤트 발생 시각 순서대로 1, 2, 3, ... 으로 숫자가 매겨진다.

(1) SUM()을 윈도우 함수의 집계 함수로 사용하여 누적합을 구한다. 

 

 

[복습]

- LAG() 윈도우 함수

- SUM() 윈도우 함수

- TIMEDIFF() 날짜/시간 차이 구하기