sessions.sql

create table user_sessions
USING DELTA AS
( select user_id, event_date, event_action,
SUM(is_new_session) OVER (ORDER BY user_id, event_date) AS global_session_id,
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_date) AS user_session_id
FROM
(  select *,
  CASE WHEN unix_timestamp(event_date) - unix_timestamp(last_event) >= (24*60*60) OR 
  last_event is NULL THEN 1 ELSE 0 END AS is_new_session
  from (
  select *, lag(event_date,1) OVER (PARTITION BY user_id ORDER BY event_date) as last_event from events
  ) last
) final
)