문제

세션 재정의하기

GA는 30분 이상 사용자가 행동하지 않을 때 세션을 종료하는데, 사용자들의 페이지 체류 시간이 증가했기 때문에 이 기준을 1시간으로 늘리려고 합니다.
세션을 종료하는 기준을 사용자가 1시간 이상 행동하지 않을 때로 수정하여, 사용자 'S3WDQCqLpK’의 세션을 재정의하고 로그 내 모든 세션의 시작 시각과 종료 시각을 출력하는 쿼리를 작성해주세요. 쿼리 결과는 세션 시작 시각 기준으로 정렬되어 있어야 합니다.

풀이 과정

아이디어 자체는 간단하다
(1) 기존 로그 데이터를 시간순으로 정렬하고
(2) 현재 timestamp와 직전 timestamp를 비교하여 1시간 이상의 차이가 발생하는 지 체크 (직전 시점과 비교하기 위해 LAG 함수 사용)
(3) 차이가 발생할 때마다 new_session_id를 += 1 해주어 별개의 session으로 묶어주기
(4) 각 session별 min -> start, max -> end

주의점

이 문제에는 사소하지만 가볍게 넘길 수 있을 포인트가 있는데

원본 데이터 정렬 여부

처음에는 주어진 데이터가 기존 로그 데이터니까 당연히 정렬이 잘 되어 있을 것이라고 가정하고 일부러 원본을 보존하면서 풀었는데,
나중에 계속 오답이 나와서 확인해보니 원본 데이터에 이미 시간 순서가 맞지 않는 경우가 있었다

당연히 체크해야 하는 부분이 맞기 때문에 뭐라할 수는 없지만...
실제 현실에서도 데이터를 쌓는 과정에서 오류가 발생하거나 순서가 꼬이는 경우가 많다고 하니 주의하도록 하자

세션 종료 시점의 정의

이 부분은 아직 명확하게 이해는 못 했지만...
문제에서 "세션을 종료하는 기준을 사용자가 1시간 이상 행동하지 않을 때로 수정"이라고 정의한 이상, 개인적으로는 세션 종료 시점을 "마지막 행동으로부터 1시간 뒤"가 되어야 한다고 생각한다
즉 datetime(max(timestamp), '1 hours')로 써야 한다고 보지만... 어쨌든 이 문제에서는 그냥 max(timestamp)를 session_end로 보았다.

---

그럼 이 부분을 숙지했다고 보고, 아래부터 문제 풀이 과정을 진행하겠다

최초 데이터 정리

가장 먼저 필요한 데이터만 추출해서 정리할 필요가 있다
상술했듯이 원본 데이터가 정렬되어 있지 않으므로 ORDER BY를 써야함은 물론,
문제에서 언급한 사용자 ID (S3WDQCqLpK)의 데이터만을 가져오면 된다.
또한 필요한 컬럼도 사용자 ID와 event_timestamp_kst 뿐이니 SELECT도 이들 컬럼들만을 적어주는 것도 좋다.

SELECT
      user_pseudo_id
      , event_timestamp_kst
    FROM 
      ga
    WHERE
      user_pseudo_id = 'S3WDQCqLpK'
    ORDER BY
      event_timestamp_kst

직전 시점 차이 계산

LAG와 LEAD 함수에 대해서는 알고 있다고 가정하고 생략.
직전 시점을 가져와야 하기에 LAG 함수를 사용하고,
이를 활용해 현재 시점과의 차이를 계산해야 한다.
SQLite는 datediff() 등의 함수가 없기에 JULIANDAY() 함수를 사용한 뒤 직접 차이를 계산하는 방식으로 계산해야 한다. (참고: [SQLite] Date Diff를 구하는 방법)

SELECT
    *
--    LAG(event_timestamp_kst) over () as "prev_time",
    , JULIANDAY (event_timestamp_kst) - JULIANDAY (LAG(event_timestamp_kst) over ()) as "time_diff"
  FROM
    (SELECT
      user_pseudo_id
      , event_timestamp_kst
    FROM 
      ga
    WHERE
      user_pseudo_id = 'S3WDQCqLpK'
    ORDER BY
      event_timestamp_kst) a

1시간 차이 발생 여부 (T/F)

방금 계산한 시간 차이가 1시간 이상인 지 체크하여 반환한다
1시간 이상이라면 1로 반환하여 추후 new_session_id를 계산할 때 활용할 예정
*참고로 1번째 row 또한 첫번째 session의 1번째 row이므로 1이 반환되어야 한다. CASE문 첫번째 조건으로 row_number()를 활용했다.

SELECT
    *
--    LAG(event_timestamp_kst) over () as "prev_time",
    , CASE
        WHEN row_number() over () = 1 then 1
        WHEN CAST(
          (
            JULIANDAY (event_timestamp_kst) - JULIANDAY (LAG(event_timestamp_kst) over ())
          ) * 24 * 60 * 60 as integer
        ) >= 3600 THEN 1
        ELSE 0
      END as "is_new_session"
  FROM
    (SELECT
      user_pseudo_id
      , event_timestamp_kst
    FROM 
      ga
    WHERE
      user_pseudo_id = 'S3WDQCqLpK'
    ORDER BY
      event_timestamp_kst) a

누적합을 통한 new_session_id 계산

is_new_session이 1인 부분은 새로운 session이 시작되는 순간이다
그러므로 해당 데이터의 위에서부터 누적합을 계산하면, 그것이 new_session_id라고 할 수 있다.
다만, 현재까지 계산한 내용만으로는 sum() over()을 사용할 수 없다 (전체 테이블에 대한 sum을 계산하게 되므로)
누적합 계산을 위해 row_number()를 통해 인덱스 값을 부여하고 이를 order by에 넣어주었다

WITH find_is_new_session AS (
  SELECT
    *
--    LAG(event_timestamp_kst) over () as "prev_time",
    , CASE
        WHEN row_number() over () = 1 then 1
        WHEN CAST(
          (
            JULIANDAY (event_timestamp_kst) - JULIANDAY (LAG(event_timestamp_kst) over ())
          ) * 24 * 60 * 60 as integer
        ) >= 3600 THEN 1
        ELSE 0
      END as "is_new_session"
    , row_number() over () as "index_value"
  FROM
    (SELECT
      user_pseudo_id
      , event_timestamp_kst
    FROM 
      ga
    WHERE
      user_pseudo_id = 'S3WDQCqLpK'
    ORDER BY
      event_timestamp_kst) a
)
SELECT
  *
  , sum(is_new_session) over(order by index_value ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "new_session_id"
FROM
  find_is_new_session

최종: 세션별 시작, 종료 시점 계산

여기까지 왔으면 남은 과정은 간단하다
new_session_id로 GROUP BY를 해주고
min(), max()를 넣어주면 세션별 시작, 종료 시점을 찾을 수 있다

WITH find_is_new_session AS (
  SELECT
    *
--    LAG(event_timestamp_kst) over () as "prev_time",
    , CASE
        WHEN row_number() over () = 1 then 1
        WHEN CAST(
          (
            JULIANDAY (event_timestamp_kst) - JULIANDAY (LAG(event_timestamp_kst) over ())
          ) * 24 * 60 * 60 as integer
        ) >= 3600 THEN 1
        ELSE 0
      END as "is_new_session"
    , row_number() over () as "index_value"
  FROM
    (SELECT
      user_pseudo_id
      , event_timestamp_kst
    FROM 
      ga
    WHERE
      user_pseudo_id = 'S3WDQCqLpK'
    ORDER BY
      event_timestamp_kst) a
),
set_new_session_id as (
SELECT
  *
  , sum(is_new_session) over(order by index_value ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as "new_session_id"
FROM
  find_is_new_session
)
SELECT
  user_pseudo_id
  , min(event_timestamp_kst) as "session_start"
  , max(event_timestamp_kst) as "session_end"
FROM
  set_new_session_id
GROUP BY
  new_session_id
;