세션 재정의하기
ga 테이블은 2022년 1월 데이터리안 웹사이트에서 발생한 Google Analytics 사용자 행동 데이터의 일부입니다. 사용자 아이디(user_pseudo_id), 세션 아이디(ga_session_id), 이벤트의 종류(event_name), 이벤트가 발생한 시각(event_timestamp_kst) 등의 정보를 가지고 있습니다.
데이터리안 웹사이트에 블로그 기능이 추가되면서 사용자들이 한 페이지에 체류하는 시간이 길어졌습니다. GA는 30분 이상 사용자가 행동하지 않을 때 세션을 종료하는데, 사용자들의 페이지 체류 시간이 증가했기 때문에 이 기준을 1시간으로 늘리려고 합니다.
세션을 종료하는 기준을 사용자가 1시간 이상 행동하지 않을 때로 수정하여, 사용자 'S3WDQCqLpK’의 세션을 재정의하고 로그 내 모든 세션의 시작 시각과 종료 시각을 출력하는 쿼리를 작성해주세요. 쿼리 결과는 세션 시작 시각 기준으로 정렬되어 있어야 합니다.
•
user_pseudo_id - 사용자 아이디 (S3WDQCqLpK)
•
session_start - 세션 시작 시각
•
session_end - 세션 종료 시각
with step1 as (
select user_pseudo_id
,event_timestamp_kst
,lag(event_timestamp_kst,1) over (order by event_timestamp_kst) last_event_timestamp
,lead(event_timestamp_kst,1) over (order by event_timestamp_kst) next_event_timestamp
,row_number() over() as id
from ga
where user_pseudo_id = 'S3WDQCqLpK'
order by event_timestamp_kst
) , step2 as (
select user_pseudo_id
,event_timestamp_kst
,timestampdiff(second, last_event_timestamp, event_timestamp_kst) as last_event
,timestampdiff(second, event_timestamp_kst, next_event_timestamp) as next_event
,id
from step1
), step3 as (
select step2.*
,case when last_event >= 3600 then id
when last_event is null then id
else lag(id,1) over (order by id) end as session
from step2
where last_event >= 3600
or last_event is null
or next_event >= 3600
or next_event is null
), step4 as(
select user_pseudo_id
,session
,MIN(event_timestamp_kst) as session_start
,MAX(event_timestamp_kst) as session_end
from step3
group by user_pseudo_id, session
)
select user_pseudo_id, session_start, session_end
from step4
order by session_start;
SQL
복사