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

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

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

-- 사용자의 이벤트 데이터를 기반으로 새로운 세션을 추적하여 세션 ID를 생성하는 쿼리

WITH cte AS (
    -- 사용자의 이벤트 데이터를 기준으로, 각 이벤트 간의 시간을 계산하여 새로운 세션을 구분
    SELECT 
        user_pseudo_id,                        -- 사용자 고유 ID
        event_timestamp_kst,                    -- 이벤트 발생 시간 (KST)
        event_name,                             -- 이벤트 이름
        ga_session_id,                          -- GA 세션 ID
        -- 이전 이벤트와의 시간 차이를 계산하여 새로운 세션을 구분
        CASE 
            WHEN 
                -- 이전 이벤트와의 시간 차이(분 단위)가 600초 이상이면 새로운 세션으로 구분
                IFNULL(
                    ROUND((julianday(event_timestamp_kst) - 
                    julianday(LAG(event_timestamp_kst, 1) OVER (ORDER BY event_timestamp_kst))) * 60 * 60 * 24, 1), 
                    601
                ) >= 600 
            THEN 1  -- 새로운 세션으로 간주
            ELSE 0  -- 기존 세션 계속 유지
        END AS new_session
    FROM ga  -- Google Analytics 데이터 테이블
    WHERE user_pseudo_id = 'a8Xu9GO6TB'  -- 특정 사용자만 필터링
)

SELECT 
    user_pseudo_id,                         -- 사용자 고유 ID
    event_timestamp_kst,                     -- 이벤트 발생 시간 (KST)
    event_name,                              -- 이벤트 이름
    ga_session_id,                           -- GA 세션 ID
    -- 새로운 세션이 시작될 때마다 세션 ID를 생성
    SUM(new_session) OVER (ORDER BY event_timestamp_kst) AS new_session_id
FROM cte  -- 위에서 계산한 새로운 세션 정보를 기반으로 결과 추출