Last active
August 29, 2015 13:56
-
-
Save yalisassoon/8907521 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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