Analytics Patterns · LAG · running SUM · sessionization
59. Sessions from raw pageviews
Concept: LAG · running SUM · sessionizationshow
Web analytics splits a user's pageviews into sessions: a new session starts when more than 30 minutes have passed since that user's previous view. Flag each row with LAG (1 = starts a session), then a running SUM of the flags numbers the sessions so you can GROUP BY them.
Example (different data):
SELECT truck_id, pinged_at,
SUM(new_trip) OVER (PARTITION BY truck_id ORDER BY pinged_at) AS trip_no
FROM (
SELECT truck_id, pinged_at,
CASE WHEN pinged_at - LAG(pinged_at) OVER (PARTITION BY truck_id ORDER BY pinged_at)
<= INTERVAL '2 hours' THEN 0 ELSE 1 END AS new_trip
FROM gps_pings
) AS p;Your task
Classic analytics engineering question: product wants sessions per user, but the tracker only logs raw pageviews.
Split pageviews into sessions per user: a gap of more than 30 minutes since the user's previous view starts a new session (exactly 30 minutes stays in the same session). Return user_id, session_start, session_end and pageviews (views in the session). Sort by user_id, session_start.
Starting Postgres in your browser…
Checking your access…