[SQL풀이][StrataScratch][medium] Users By Average Session Time

StrataScratch - Users By Average Session Time

문제이해

• 유저별 평균 session time을 계산하기
• session 정의 : action 컬럼의 page_load 이벤트와 page_exit 이벤트 사이
  • 한 세션 內 page_load 이벤트나 page_exit 이벤트가 2개 이상인 경우
    • page_load : 가장 최근의 page_load 기준
    • page_exit : 가장 빠른 page_exit 기준 
  • 유저당 최대 1일 1세션 (편의상)

 

접근방법

• user_id - 날짜  조합을 unique key로 page_load와, page_exit의 timestamp를 각각 계산
• 위에서 계산된 timestamp를 이용하여 session time 계산
  • session time = page_exit - page_load
• user_id 기준으로 session time의 평균을 계산

 

풀이코드

-- latest page_load 계산
WITH load AS(
    SELECT 
        user_id, 
        DATE(timestamp) AS date,
        MAX(timestamp) AS latest_loadtime
    FROM facebook_web_log
    WHERE action = 'page_load'
    GROUP BY user_id, DATE(timestamp)
    ),

-- latest page_exit 계산
exit AS(
    SELECT 
        user_id, 
        DATE(timestamp) AS date,
        MIN(timestamp) AS earliest_exittime
    FROM facebook_web_log
    WHERE action = 'page_exit'
    GROUP BY user_id, DATE(timestamp)
)

-- session time 계산 후 user_id별 평균 계산
SELECT 
    user_id,
    AVG((e.earliest_exittime - l.latest_loadtime)) AS session_time
FROM load AS l
LEFT JOIN exit AS e
USING (user_id, date)
GROUP BY user_id
HAVING AVG((e.earliest_exittime - l.latest_loadtime)) IS NOT NULL
;