Checking client-side sessionization

-- For every event, identify the previous event timestamp and session index

SELECT
  dvce_tstamp AS current_tstamp,
  LAG(dvce_tstamp, 1)
  	OVER (PARTITION BY domain_userid ORDER BY dvce_tstamp) AS previous_tstamp,
  domain_sessionidx AS current_session_index,
  LAG(domain_sessionidx, 1)
  	OVER (PARTITION BY domain_userid ORDER BY dvce_tstamp) AS previous_session_index
FROM atomic.events;

-- We can query that data to identify if there are any cases where the session_index has failed 
-- to increment after a user has been inactive for more than 30 minutes 

SELECT
*
FROM (
  SELECT
    dvce_tstamp AS current_tstamp,
    LAG(dvce_tstamp, 1)
    	OVER (PARTITION BY domain_userid ORDER BY dvce_tstamp) AS previous_tstamp,
    domain_sessionidx AS current_session_index,
    LAG(domain_sessionidx, 1)
    	OVER (PARTITION BY domain_userid ORDER BY dvce_tstamp) AS previous_session_index
  FROM atomic.events
) t
WHERE current_session_index = previous_session_index 
AND   EXTRACT(MIN FROM (current_tstamp - previous_tstamp)) > 30;