Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save yalisassoon/8907521 to your computer and use it in GitHub Desktop.
Save yalisassoon/8907521 to your computer and use it in GitHub Desktop.
-- The following query orders events by `domain_userid`, `domain_sessionidx` and `dvce_tstamp`, then checks the difference
-- (in minutes) between corresponding timestamps. If these are greater than 30 minutes for events in the same session
-- (i.e. `domain_userid` is the same on both rows and `domain_sessionidx` is the same on both rows) then there must be a problem
-- in the Javascript that determines when to increment the `domain_sessionidx` value.
SELECT
domain_userid,
LAG(domain_userid) OVER (ORDER BY domain_userid, domain_sessionidx, dvce_tstamp) AS previous_duserid,
domain_sessionidx AS current_dsessionidx,
LAG(domain_sessionidx) OVER (ORDER BY domain_userid, domain_sessionidx, dvce_tstamp) AS previous_dsessionidx,
dvce_tstamp AS current_tstamp,
LAG(dvce_tstamp,1) OVER (ORDER BY domain_userid, domain_sessionidx, collector_tstamp) AS previous_tstamp,
EXTRACT(EPOCH FROM dvce_tstamp - LAG(collector_tstamp,1) OVER (ORDER BY domain_userid, domain_sessionidx, collector_tstamp))/60 AS difference_in_minutes
FROM atomic.events
WHERE dvce_tstamp > '2014-01-01'
ORDER BY domain_userid, domain_sessionidx, dvce_tstamp;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment