Skip to content

Instantly share code, notes, and snippets.

@Scarysize
Created June 28, 2020 14:19
Show Gist options
  • Save Scarysize/08d2951f3a865b41a9065c0a959e2b81 to your computer and use it in GitHub Desktop.
Save Scarysize/08d2951f3a865b41a9065c0a959e2b81 to your computer and use it in GitHub Desktop.
-- The view
CREATE VIEW session(
id,
visitor_id,
start_utc_time,
next_start_utc_time
) AS
SELECT
pi_with_inactivity.visitor_id || '-' || row_number() OVER(
PARTITION BY pi_with_inactivity.visitor_id
ORDER BY
pi_with_inactivity.utc_time
) AS session_id,
pi_with_inactivity.visitor_id,
pi_with_inactivity.utc_time AS session_start_at,
lead(utc_time) OVER(
PARTITION BY pi_with_inactivity.visitor_id
ORDER BY
pi_with_inactivity.utc_time
) AS next_session_start_at
FROM (
SELECT
utc_time,
visitor_id,
LAG(utc_time) OVER (
PARTITION BY visitor_id
ORDER BY
utc_time
) - utc_time AS inactive_seconds
FROM page_view
) AS pi_with_inactivity
WHERE
ABS(pi_with_inactivity.inactive_seconds) > 30 * 60
OR pi_with_inactivity.inactive_seconds IS NULL
-- The query
SELECT
count(id) as sessions
FROM
session
WHERE
date(start_utc_time, 'unixepoch', 'localtime') <= ? AND
date(start_utc_time, 'unixepoch', 'localtime') >= ?
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment