[SQL] solvesql Advent of SQL 2024 21일차

# 링크

• solvesql Advent of SQL 2024: https://solvesql.com/collections/advent-of-sql-2024/

https://solvesql.com/collections/advent-of-sql-2024/

• 21일차 - 세션 유지 시간을 10분으로 재정의하기: https://solvesql.com/problems/redefine-session-2/

https://solvesql.com/problems/redefine-session-2/

 

 

# 풀이

WITH
  user_events AS (
    SELECT
      user_pseudo_id,
      event_timestamp_kst,
      event_name,
      ga_session_id,
      DATETIME(event_timestamp_kst) AS event_time
    FROM
      ga
    WHERE
      user_pseudo_id = 'a8Xu9GO6TB'
  ),
  session_boundary AS (
    SELECT
      *,
      COALESCE(
        LAG(event_time) OVER (
          ORDER BY
            event_time
        ),
        event_time
      ) AS prev_event_time
    FROM
      user_events
  )
SELECT
  user_pseudo_id,
  event_timestamp_kst,
  event_name,
  ga_session_id,
  SUM(
    CASE
      WHEN JULIANDAY (event_time) - JULIANDAY (prev_event_time) > (10.0 / 1440) THEN 1
      ELSE 0
    END
  ) OVER (
    ORDER BY
      event_time
  ) + 1 AS new_session_id
FROM
  session_boundary;

• user_events CTE
  • SELECT
    
    • user_pseudo_id: user_pseudo_id 컬럼을 선택한다.
    • event_timestamp_kst: event_timestamp_kst 컬럼을 선택한다.
    • event_name: event_name 컬럼을 선택한다.
    • ga_session_id: ga_session_id 컬럼을 선택한다.
    • DATETIME(event_timestamp_kst) AS event_time: event_timestamp_kst를 DATETIME 형식으로 변환한 값을 event_time으로 저장한다.
  • FROM ga: ga 테이블에서 데이터를 가져온다.
  • WHERE user_pseudo_id = 'a8Xu9GO6TB': user_pseudo_id가 'a8Xu9GO6TB'인 데이터만 선택한다.
•   session_boundary CTE
  
  • SELECT
    
    • *: 전체 컬럼을 선택한다.
    • COALESCE(LAG(event_time) OVER (ORDER BY event_time), event_time) AS prev_event_time: event_time을 기준으로 이전 event_time 값을 반환하고, 이전 값이 없는 경우에는 원래의 값을 반환한 후, 해당 값을 prev_event_time으로 저장한다.
  • FROM user_events: user_events CTE에서 데이터를 가져온다.
• SELECT
  
  • user_pseudo_id: user_pseudo_id 컬럼을 가져온다.
  • event_timestamp_kst: event_timestamp_kst 컬럼을 가져온다.
  • event_name: event_name 컬럼을 가져온다.
  • ga_session_id: ga_session_id 컬럼을 가져온다.
  • SUM(CASE WHEN JULIANDAY (event_time) - JULIANDAY (prev_event_time) > (10.0 / 1440) THEN 1 ELSE 0 END) OVER (ORDER BY event_time) + 1 AS new_session_id: event_time을 Julian 날짜 형식으로 변환한 값에서 prev_event_time을 Julian 날짜 형식으로 변환한 값을 뺀 값이 '10.0 / 1440'보다 큰 경우 1을 반환하고 그렇지 않은 경우 0을 반환한 후, event_time을 기준으로 정렬하여 누적 합산하고, 해당 값에 1을 더하여 누적 합계가 1부터 시작하도록 조정한 후, 해당 값을 new_session_id로 저장한다.
    1. JULIANDAY(): 입력된 날짜를 Julian 날짜 형식으로 변환한다. Julian 날짜는 기원전 4713년 1월 1일부터 특정 날짜까지 경과된 날을 실수로 반환하며, 시간까지 포함할 경우 소수점으로 표현한다.
    2. JULIANDAY (event_time) - JULIANDAY (prev_event_time): event_time과 prev_event_time의 시간 차이를 일 단위로 계산한다.
    3. CASE WHEN JULIANDAY (event_time) - JULIANDAY (prev_event_time) > (10.0 / 1440) THEN 1 ELSE 0 END : event_time과 prev_event_time의 시간 차이가 10분(1일 = 1440분)을 초과하면 1을 반환하고, 그렇지 않으면 0을 반환한다. → 새로운 세션의 시작일 경우 1, 그렇지 않으면 0으로 표시된다.
    4. SUM(...): CASE문의 결과(0 또는 1)를 누적 합산한다. → 새로운 세션이 시작될 때마다 1이 더해지므로, 누적 합이 세션ID 역할을 하게 된다.
    5. OVER (ORDER BY event_time): event_time을 기준으로 정렬하여 누적 계산을 진행한다. → 각 이벤트가 발생한 시간 순서대로 세션 ID가 부여된다.
    6. + 1: 누적 합계의 기본값은 0부터 시작하므로, 세션 ID가 1부터 시작될 수 있도록 계산 결과에 1을 더한다.
• FROM session_boundary: session_boundary CTE에서 데이터를 가져온다.