구름 아카이브

저작권 문제로 인해, 직접 작성한 쿼리문만 공개.

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

WITH user_event AS (
    SELECT 
      user_pseudo_id,
      event_timestamp_kst,
      event_name,
      ga_session_id,
      LAG(event_timestamp_kst) OVER(ORDER BY event_timestamp_kst) AS prev_event_time
    FROM ga
    WHERE user_pseudo_id = 'a8Xu9GO6TB'
)

, group_reset AS (
    SELECT
      user_pseudo_id,
      event_name,
      event_timestamp_kst,
      ga_session_id,
      CASE
          WHEN TIMESTAMPDIFF(MINUTE, prev_event_time, event_timestamp_kst) > 10 OR prev_event_time IS NULL THEN 1 ELSE 0 
      END AS new_group
    FROM user_event
)

, regrouping AS (
    SELECT
      user_pseudo_id,
      event_timestamp_kst,
      event_name,
      ga_session_id,
      SUM(new_group) OVER(ORDER BY event_timestamp_kst) AS new_session_id
    FROM group_reset
    
)

SELECT
      user_pseudo_id,
      event_timestamp_kst,
      event_name,
      ga_session_id,
      new_session_id
FROM regrouping
ORDER BY event_timestamp_kst