Last active
February 21, 2018 14:04
-
-
Save RedaAffane/f3b129cec5ac960b59ed91ada58d656d 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
SELECT *, | |
"user_id" || '_' || SUM("new_micro_session") | |
OVER (PARTITION BY "user_id" | |
ORDER BY "timestamp") AS "user_micro_session" | |
FROM ( | |
SELECT | |
*, | |
CASE WHEN | |
EXTRACT(epoch FROM "timestamp") | |
- LAG( | |
EXTRACT(epoch FROM "timestamp") | |
) OVER ( | |
PARTITION BY "user_id" | |
ORDER BY "timestamp" | |
) >= 30 * 60 THEN 1 ELSE 0 END as new_micro_session | |
FROM log_data | |
)t |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment