[StrataScratch - SQL] (Medium) Users By Average Session Time

문제

각 사용자의 평균 세션 시간을 계산하십시오. 세션은 page_load와 page_exit 사이의 시간 차이로 정의됩니다. 각 사용자는 하루에 하나의 세션만 있다고 가정합니다. 동일한 날에 여러 개의 page_load 또는 page_exit 이벤트가 있는 경우, 최신 page_load와 가장 이른 page_exit만 사용하되, page_load가 page_exit보다 먼저 발생해야 합니다. user_id와 그들의 평균 세션 시간을 출력하십시오.

 

테이블설명

 

예상결과

 

✏️ 풀이

WITH session_duration AS (
    SELECT user_id, timestamp, action,
           IF(action = 'page_load', @last_load := timestamp, NULL) AS load_time,
           IF(action = 'page_exit' AND @user_id = user_id AND @last_load IS NOT NULL, TIMESTAMPDIFF(SECOND, @last_load, timestamp), NULL) AS session_duration,
           @user_id := user_id
    FROM facebook_web_log
    ORDER BY user_id, timestamp
), filter_session AS (
    SELECT *
    FROM session_duration
    WHERE session_duration IS NOT NULL
)

SELECT user_id, AVG(session_duration) AS avg_session_duration
FROM filter_session
GROUP BY 1;

1. @last_load에 가장 최근 마지막 load time 저장

2. 'page_exit' action에서 user_id가 같은지 확인하고 @last_load의 값이 있는지 확인

3. 2번을 만족하면 page_load의 timestamp와 page_exit의 timestamp의 차이를 구함

4. 3번이 없는 값은 필터

5. 4번 데이터에서 평균 구하기

 

easy에서 medium으로 넘어오니 난이도가 꽤 많이 상승한 것 같다