[SQL문제풀기] 세션 재정의하기

문제

solvesql.com

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

SQLite

내가 작성한 정답

1. a 테이블에서 row_number으로 시간의 흐름에 따라 번호를 매겼다.

2. b 테이블에서 a 테이블끼리 join하여 시간차이가 1시간이상 난 레코드들을 걸러내고

3. row_number으로 번호를 매겼다. → 다음에 self join하기 위함

4. 최종쿼리에서 b테이블을 셀프조인해서 시작과 끝을 맞췄다.

with a as (select row_number() over (order by event_timestamp_kst) t,
	                event_timestamp_kst, 
	                user_pseudo_id
          from ga
          where user_pseudo_id = 'S3WDQCqLpK'),
    b as (select row_number() over(order by a.event_timestamp_kst) tt,
                  b.event_timestamp_kst ss,
                  a.event_timestamp_kst ee,
                  b.user_pseudo_id
          from a
          full join a b on a.t+1 = b.t
          where strftime('%s',b.event_timestamp_kst) - strftime('%s',a.event_timestamp_kst) >= 3600
                or b.event_timestamp_kst is null 
                or a.event_timestamp_kst is null )
select c.user_pseudo_id, c.ss session_start, b.ee session_end
from b 
join b c on b.tt = c.tt+1

GPT가 작성한 정답

1. 시간에서 이전 행(LAG(event_timestamp_kst) OVER (ORDER BY event_timestamp_kst))의 시간을 빼서 한시간 이상 차이가 난다면 1을 부여하고 아니라면 0을 부여한다. 이전 행이 Null이어도 1을 부여

2. 이 값들을 윈도우 함수 sum() over를 이용하여 누적합을 구한다

// 처음 행부터 지금 행까지
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

3. 누적합으로 만들 수 있는 그룹을 만들어 최소값 - 세션 시작, 최대값 - 세션 종료를 구한다.

WITH ordered_events AS (
  SELECT
    event_timestamp_kst,
    LAG(event_timestamp_kst) OVER (ORDER BY event_timestamp_kst) AS prev_event_ts,
    CASE
      WHEN strftime('%s', event_timestamp_kst) - strftime('%s', LAG(event_timestamp_kst) OVER (ORDER BY event_timestamp_kst)) > 3600
        OR LAG(event_timestamp_kst) OVER (ORDER BY event_timestamp_kst) IS NULL
      THEN 1 ELSE 0
    END AS is_new_session,
    user_pseudo_id
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
),
sessionized AS (
  SELECT
    *,
    SUM(is_new_session) OVER (ORDER BY event_timestamp_kst ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_num
  FROM ordered_events
),
session_ranges AS (
  SELECT
    user_pseudo_id,
    session_num,
    MIN(event_timestamp_kst) AS session_start,
    MAX(event_timestamp_kst) AS session_end
  FROM sessionized
  GROUP BY user_pseudo_id, session_num
)
SELECT
  user_pseudo_id,
  session_start,
  session_end
FROM session_ranges
ORDER BY session_start;

LAG(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)
	•	column_name: 이전 값을 가져올 컬럼
	•	offset: 몇 번째 이전 값? (생략 시 기본은 1)
	•	default_value: 이전 값이 없을 경우 대체할 값 (예: 첫 번째 행)

LEAD(column_name, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)	
  •	column_name: 다음 값을 가져올 컬럼
	•	offset: 몇 번째 다음 값? (생략 시 기본은 1)
	•	default_value: 다음 값이 없을 경우 대체할 값 (예: 첫 번째 행)