Split Up User Sessions

session-split-up.sql

WITH filtering AS
(
	SELECT * FROM mm.ods_event_log_v2_recent WHERE did = '{{ did }}'
),
with_last_event AS
(
	SELECT *,
		LAG(event_time, 1) OVER
			(PARTITION BY did ORDER BY event_time) AS last_event
	FROM filtering
),
final AS
(
    SELECT CASE WHEN EXTRACT('EPOCH' FROM event_time) - EXTRACT('EPOCH' FROM last_event) >= (60 * 10) 
    	OR last_event ISNULL 
    	THEN 1 ELSE 0 END AS is_new_session, *
    FROM with_last_event
)
SELECT event_time, did, event_name,
       SUM(is_new_session) OVER (PARTITION BY did ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS user_session_id
FROM final;