-- 첫 서치 후, 30일 이내 재 검색한 횟수 별 user 수
SELECT searches,
COUNT(*) AS users
FROM ( -- z , user_id 별 search_run 이벤트가 발생한 '세션' 수
SELECT user_id,
COUNT(*) AS searches
FROM ( -- z, 세션마다 search_run 이벤트가 발생한 횟수
SELECT x.session_start,
x.session,
x.user_id,
x.first_search,
COUNT(CASE WHEN x.event_name = 'search_run' THEN x.user_id ELSE NULL END) AS runs
FROM ( -- x, 첫번째 검색 후 30일 이내에 다시 검색한 세션 추출
SELECT e.*,
first.first_search,
session.session,
session.session_start
FROM tutorial.yammer_events e
-- User_id 별 '2014-08-01' 이전 처음으로 'search_run' 이벤트 발생한 시각
JOIN (
SELECT user_id,
MIN(occurred_at) AS first_search
FROM tutorial.yammer_events
WHERE event_name = 'search_run'
GROUP BY 1
) first
ON first.user_id = e.user_id
AND first.first_search <= '2014-08-01'
-- User_id 별 생성된 세션, 해당 세션이 시작된 시각과 끝난 시각
LEFT JOIN ( -- session
SELECT user_id,
session,
MIN(occurred_at) AS session_start,
MAX(occurred_at) AS session_end
FROM ( --final
SELECT bounds.*,
CASE WHEN last_event >= INTERVAL '10 MINUTE' THEN id
WHEN last_event IS NULL THEN id
ELSE LAG(id,1) OVER (PARTITION BY user_id ORDER BY occurred_at) END AS session
FROM ( -- bounds
SELECT user_id,
event_type,
event_name,
occurred_at,
occurred_at - LAG(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) AS last_event,
LEAD(occurred_at,1) OVER (PARTITION BY user_id ORDER BY occurred_at) - occurred_at AS next_event,
ROW_NUMBER() OVER () AS id
FROM tutorial.yammer_events e
WHERE e.event_type = 'engagement'
ORDER BY user_id,occurred_at
) bounds -- 완
WHERE last_event >= INTERVAL '10 MINUTE'
OR next_event >= INTERVAL '10 MINUTE'
OR last_event IS NULL
OR next_event IS NULL
) final --완
GROUP BY 1,2
) session -- 완
ON session.user_id = e.user_id
AND session.session_start <= e.occurred_at
AND session.session_end >= e.occurred_at
AND session.session_start <= first.first_search + INTERVAL '30 DAY'
WHERE e.event_type = 'engagement'
) x -- 완
GROUP BY 1,2,3,4
) z -- 완
WHERE z.runs > 0
GROUP BY 1
) z -- 완
GROUP BY 1
ORDER BY 1
LIMIT 100
yammer_events 테이블 내용
first - 각 유저별 첫 search_run 시간
bounds
final - 세션별 처음과 끝 시간
session - 유저와 세션별로 세션 시작과 끝시간
x - 2014-08-01이전에 첫 검색을 한 유저중 30일 이내에 또 검색한 유저
z - 세션별 사용자별 search_run한 개수
z - search_run이 1이상인 user의 search_run을 한 세션 개수
z(최종) - search_run을 1번이상 했었던 유저들의 수(세션 하나는 하나로 침)
최종 그래프의 의미: 첫 검색 후 한달 내 몇개 세션을 만들었는지
→ 첫 검색 후, 검색이벤트를 한 세션이 몇개 였는지 (여기서 세션1이 첫검색을 포함하는 건지 확인해봐야겠따 → 안하는 것 같다)
쿼리 느낀점