[solvesql - day 21] lv5 세션 유지 시간을 10분으로 재정의하기

👉 문제 바로가기 : solvesql - Advent of SQL 2024 DAY 21
저작권 문제로 링크만 첨부한다.

✔️문제 풀이에 필요한 점

윈도우 함수를 적절히 사용할 수 있어야 한다.

간만에 난이도가 5로 높은 문제가 나와서 설렜는데
윈도우 함수를 사용해야 해서 높게 책정된 것으로 보이고

윈도우 함수에 익숙하다면 어려울 것이 없는 문제이다.

✍️답안

WITH calculated AS (
  SELECT
    user_pseudo_id
    , event_timestamp_kst
    , event_name
    , ga_session_id
    , CASE 
      WHEN ifnull(round((julianday(event_timestamp_kst) - julianday(lag(event_timestamp_kst, 1) OVER(ORDER BY event_timestamp_kst)) ) * 60 * 24, 1),11) > 10 THEN 1
      ELSE 0
    END new_session_flag
  FROM
    ga
  WHERE
    user_pseudo_id = 'a8Xu9GO6TB'
)
SELECT
  user_pseudo_id
  , event_timestamp_kst
  , event_name
  , ga_session_id
  , sum(new_session_flag) OVER(ORDER BY event_timestamp_kst) "new_session_id"
FROM
  calculated
;

답안 설명

임시테이블의 CASE문을 작은것부터 설명하자면

1. 바로 전 행을 구한다.

lag(event_timestamp_kst, 1) OVER(ORDER BY event_timestamp_kst)

2. 바로 전 행과 이번 행의 시간 차이를 계산한다.

julianday(event_timestamp_kst) - julianday(lag(event_timestamp_kst, 1) OVER(ORDER BY event_timestamp_kst)) ) 

• julianday 함수를 사용해서 날짜를 숫자형으로 바꾸어서 계산

3. 1일단위를 분단위로 바꾼다.

(julianday(event_timestamp_kst) - julianday(lag(event_timestamp_kst, 1) OVER(ORDER BY event_timestamp_kst)) ) * 60 * 24

4. CASE문에 넣어서 10분이 넘으면 새로운 세션으로 판단(1)한다.

CASE 
  WHEN ifnull(round((julianday(event_timestamp_kst) - julianday(lag(event_timestamp_kst, 1) OVER(ORDER BY event_timestamp_kst)) ) * 60 * 24, 1),11) > 10 THEN 1
  ELSE 0
END new_session_flag

• 이 때 첫 행은 새로운 세션이지만, 전 행이 없기 때문에 시간차이가 null이 되는 것을 처리해준다.
  ( 시간차이가 null값일 경우 11로 입력해주어 조건문을 통과하게 처리 )

위 과정을 통해 정의된 new_session_flag는 새로운 세션이 시작될 때마다 1의 값을 가진다.
이를 메인쿼리에서 누적합(sum over)으로 더하면 모든 행의 새로운 세션아이디가 계산된다.