solvesql - 세션 재정의하기(WITH, WINDOW)

문제 출처 : https://solvesql.com/problems/redefine-session/

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

 

 

 

 

1. 문제

난이도5, 정답률 46.62% 

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 - 세션 종료 시각



참고 자료
-  How a web session is defined in Universal Analytics

 

2. 조건

• 다른 세션 ID여도 이전에 사용하던 세션이 1시간 차이가 안나면 해당 세션 그대로 사용
• 각 세션 시간차이가 1시간 이상 나는 데이터들만 선정(처음과 끝 포함)
• user_pseudo_id = 'S3WDQCqLpK'인 데이터만 선정

 

3. 쿼리

-- 세션의 시간 차이 구하기
-- 다른 세션 ID여도 이전에 사용하던 세션이 1시간 차이가 안나면 해당 세션 그대로 사용
-- 각 세션 시간차이가 1시간 이상 나는 데이터들만 선정(처음과 끝 포함)
WITH ga_data AS(
  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 ASC) AS last_event
    ,LEAD(event_timestamp_kst, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp_kst ASC) AS next_event
    ,ROW_NUMBER() OVER() AS rownum
  FROM ga
  WHERE user_pseudo_id = 'S3WDQCqLpK'
),
event_time_diff AS (
  SELECT
     user_pseudo_id
    ,event_timestamp_kst
    ,(julianday(event_timestamp_kst) - julianday(last_event)) * 86400 AS lasttimediff
    ,(julianday(next_event) - julianday(event_timestamp_kst)) * 86400 AS nexttimediff
    ,rownum
  FROM ga_data
),
event_data AS(
  SELECT
    *
    ,CASE WHEN lasttimediff > 3600 THEN rownum
         WHEN lasttimediff IS NULL THEN rownum
         ELSE LAG(rownum, 1) OVER(PARTITION BY user_pseudo_id ORDER BY rownum ASC) END AS sessionNUM
  FROM event_time_diff
  WHERE lasttimediff IS NULL 
  OR lasttimediff >= 3600
  OR nexttimediff IS NULL
  OR nexttimediff >= 3600
)

SELECT 
    user_pseudo_id
    ,MIN(event_timestamp_kst) AS session_start 
    ,MAX(event_timestamp_kst) AS session_end
FROM event_data
GROUP BY user_pseudo_id, sessionNUM

해당 문제는 문제의 설명 자체를 이해하는데서 조금 어려운 부분이 있었다. 처음에는 세션 ID를 기준으로 봐야 하는줄 알고 간단하게 풀 수 있을 줄 알았으나 user_pseudo_id만 기준으로 하여 세션이 1시간 이내에 만료하기 전에 들어온 데이터를 찾아주어야 하는 작업으로 진행해야 해서 푸는데 조금 오래 걸린것 같다.

서브쿼리를 사용해서 진행해도 풀 수는 있을것 같지만 조금 복잡해보이고 짠 쿼리를 다시 이해해야하는 과정이 있을 것 같아서 WITH문으로 임시쿼리를 호출하여 작업을 진행하였다.

WITH문은 3가지로 구성이 되었는데
첫번째는 특정 user_pseudo_id를 기준으로 하여 LAG과 LEAD를 활용하여 이전 데이터의 세션 이벤트 타임, 이후 데이터의 세션 이벤트 타임을 가져오고 불러온 데이터에 새로운 ID(rownum)을 부여해준다.

두번째는 첫번째에서 추출한 데이터를 기준으로 각 이벤트 타임의 시간 차이를 구해준다. 해당 시간 차이가 각각 1시간이 차이가 나는 데이터들을 기준으로 작업을 진행해야 하기 때문이다.

세번째는 시간차이를 기준으로 1시간이 나지 않는 경우에 이전의 rownum을 가져와서 추출할 데이터에 넣어주는 작업이다. 한마디로 1시간이상이 나거나 시간 차이가 null(처음이나 끝 데이터)인 경우에는 기존에 새로 만들어진 ID값을 사용하고 1시간 이상 차이 나지 않는경우에는 이전에 생성된 세션의 ID를 사용하겠다는 의미이다.

문제 자체가 조금 어렵긴한데 WITH문과 Window함수를 활용하여 문제를 해결하는게 생각보다 재밌고 많이 공부가 되는 문제인것 같다.

 

4. 실행 결과