Group page views into sessions separated by a period of inactivity.

“I’ll assume page_views(view_id, user_id, viewed_at, url), a 30-minute threshold, and that a gap strictly longer than the threshold starts a new session. I’ll also assume user_id is never null here; if anonymous views exist I’d partition by a device or cookie id instead, because every null lands in the same partition. First I flag session starts.”

WITH flagged AS (
  SELECT view_id, user_id, viewed_at, url,
         coalesce(
           viewed_at - lag(viewed_at) OVER w
             > interval '30 minutes',
           true)::int AS new_session
  FROM page_views
  WINDOW w AS (PARTITION BY user_id
               ORDER BY viewed_at, view_id)
),
numbered AS (
  SELECT *,
         sum(new_session) OVER (
           PARTITION BY user_id
           ORDER BY viewed_at, view_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
         ) AS session_seq
  FROM flagged
)
SELECT user_id,
       session_seq,
       min(viewed_at) AS started_at,
       max(viewed_at) AS last_view_at,
       count(*)       AS views
FROM numbered
GROUP BY user_id, session_seq
ORDER BY user_id, session_seq;

“Walking through it: lag() gives the previous view for this user, and it’s null for the first view, so the comparison is null and coalesce turns it into a session start. The running sum of the flag is 1 for every row in the first session, 2 in the second, and so on, so grouping by user and that number gives one row per session. The session key is (user_id, session_seq), kept as two typed columns so it joins cleanly.”

“Here it is on one user’s five views. The gap after 10:40 is 31 minutes, the only gap over the threshold, so five views become two sessions.”

“Grouped, that’s session 1 from 10:00 to 10:40 with 3 views, and session 2 at 11:11 with 2. The 30-minute gap stays inside a session because the rule is strictly longer, and the two views at 11:11 keep their order because view_id breaks the tie.”

“view_id is in both ORDER BY clauses so the order is the same on every run. I also wrote the frame as ROWS, because a row-by-row running sum is what I mean. Without it the default is RANGE ... CURRENT ROW, which pulls in every row that ties on the sort keys, as the PostgreSQL window function syntax describes. Here it would give the same answer, because view_id is unique, so no two rows are peers in the order. I’d still rather the query say what it does.”

“The shape, lag then flag then running sum, is plain window SQL, so it carries to Spark SQL, Databricks and BigQuery; what changes is the interval and cast syntax. BigQuery has no :: cast, so the flag becomes CAST(... AS INT64), and the gap test becomes viewed_at > TIMESTAMP_ADD(LAG(viewed_at) OVER w, INTERVAL 30 MINUTE). I’d avoid TIMESTAMP_DIFF(..., SECOND) > 1800 there, because it truncates to whole seconds, so a gap of 30 minutes and half a second wouldn’t count as longer. Snowflake goes further: CONDITIONAL_TRUE_EVENT counts the rows so far where a condition is true, and the condition may use LAG over the same window, so the flag and the running sum become one function. It numbers from 0, and the first view’s null gap is not true, so the first session is 0, not 1.”

“The edges I’d test: one user with a single view gets one session; views at 10:10 and 10:40 stay together, because the gap is not strictly longer than the threshold; two views with the same timestamp stay in one session in a fixed order.”

“A session’s duration is last_view_at - started_at, which is zero for a single-view session. We don’t see time spent on the last page, so I’d call it that rather than ‘time on site’.”

“For the follow-ups: to cap runaway sessions, the exact version is recursive, because each split moves the start of the next piece. The practical version cuts each session into fixed pieces from its first view. With a four-hour cap, 14400 seconds, I add one CTE after numbered and group by the piece as well:”

pieced AS (
  SELECT *,
         floor(extract(epoch FROM
               viewed_at - min(viewed_at) OVER s)
               / 14400)::int AS piece
  FROM numbered
  WINDOW s AS (PARTITION BY user_id, session_seq)
)
SELECT user_id, session_seq, piece,
       min(viewed_at) AS started_at,
       max(viewed_at) AS last_view_at,
       count(*)       AS views
FROM pieced
GROUP BY user_id, session_seq, piece
ORDER BY user_id, session_seq, piece;

“A view every ten minutes from 08:00 to 18:00 then comes out as three pieces, not one ten-hour session. A script that pings all day is also one user with a huge number of rows, and in Spark that one partition does all the window work on one task. So I’d filter known bots, or cap rows per user, before the window runs.”

“For hourly loads, appending sessions for the new rows alone would split a session that spans the boundary, and a late view can join two sessions I already wrote. So for each user with new rows, I find their earliest new view, take the latest stored session that started at or before it (the one it falls inside or could extend), delete that session and everything after it, and rebuild from there, numbering on from the sessions I kept. I’d treat a session as final only once the loaded data runs past its last view by more than the threshold.”

“For sessions per day, I’d count each session on the day it started and say so; splitting at midnight is a different definition. Before I ship either, I’d ask which tool’s session count the customer already looks at and match its rules, or put the difference in writing. If that tool is GA4, Google’s comparison of GA4 and Universal Analytics says a GA4 session ends after more than 30 minutes of inactivity, depending on the timeout setting, and is not restarted at midnight, where Universal Analytics did restart it. Otherwise the first meeting is spent explaining why the two numbers disagree.”