💫세션 재정의하기

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

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

 

[풀이]

SELECT
    user_pseudo_id,
    MIN(event_timestamp_kst) AS session_start,
    MAX(event_timestamp_kst) AS session_end
FROM (
    SELECT
        step2.*,
        CASE 
            WHEN last_diff IS NULL THEN id
            WHEN last_diff >= 3600 THEN id
            ELSE LAG(id, 1) OVER (PARTITION BY user_pseudo_id ORDER BY id) 
        END AS session
    FROM (
        SELECT
            user_pseudo_id,
            event_timestamp_kst,
            id,
            (strftime('%s', event_timestamp_kst) - strftime('%s', last_event)) AS last_diff,
            (strftime('%s', next_event) - strftime('%s', event_timestamp_kst)) AS next_diff
        FROM (
            SELECT
                user_pseudo_id,
                event_name,
                event_timestamp_kst,
                LAG(event_timestamp_kst, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS last_event,
                LEAD(event_timestamp_kst, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst) AS next_event,
                ROW_NUMBER() OVER () AS id
            FROM
                ga
            WHERE
                user_pseudo_id = 'S3WDQCqLpK'
        ) AS step1
    ) AS step2
    WHERE 
        last_diff IS NULL OR next_diff IS NULL OR last_diff >= 3600 OR next_diff >= 3600
) AS step3
GROUP BY
    user_pseudo_id, session
ORDER BY
    session_start;

 

☑️step3에서 WHERE절의 의미 

WHERE last_diff IS NULL OR next_diff IS NULL OR last_diff >= 3600 OR next_diff >= 3600