Skip to content

Instantly share code, notes, and snippets.

@RedaAffane
Last active February 21, 2018 14:04
Show Gist options
  • Save RedaAffane/f3b129cec5ac960b59ed91ada58d656d to your computer and use it in GitHub Desktop.
Save RedaAffane/f3b129cec5ac960b59ed91ada58d656d to your computer and use it in GitHub Desktop.
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