SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…