Sessionizing log file data using SQL

final-sessionization-query.sql

--Query 3:  Outer query uses window function with sum to do cumulative sum as the id, concatentate to uid
select
uid,
uid || '-' || cast(sum(new_event_boundary) OVER (PARTITION BY uid ORDER BY event_timestamp) as varchar) as session_id,
event_timestamp
from 
			--Query 1: Define boundary events
			(select
			uid,
			event_timestamp,
			case when extract(epoch from event_timestamp) - lag(extract(epoch from event_timestamp)) OVER (PARTITION BY uid ORDER BY event_timestamp) > 30 * 60 then 1 ELSE 0 END as new_event_boundary
			from single_col_timestamp
			) a;