Turn Clickstream Events into Sessions with DuckDB

Ada reads a tutorial, runs the example twice, and sends an enquiry. Forty-five minutes after her first click, she opens another page. Is that still one visit? The answer depends on the gaps between events, not simply the time since arrival.

We will turn a tiny event log into sessions, then calculate something useful about each one. Change the threshold in the illustration before running the queries.

Start with events you can inspect

Our fictional website records view, run, and enquiry events. user_id is a stable identifier; id uniquely identifies an event. Timestamps share one UTC clock, stored here as timezone-free TIMESTAMP values for a self-contained exercise.

The rules are deliberate: the first event starts a session; a gap greater than 30 minutes starts another; a gap of exactly 30 minutes stays in the current session. Sessions may cross midnight. This is our analytical definition, not a reproduction of Google Analytics session rules.

Look backward one event

lag reads the preceding row in a window. First, examine Ada’s January 5 events:

SELECT id, event, happened_at::TIME::VARCHAR AS time,
  date_diff('minute', lag(happened_at) OVER (
    ORDER BY happened_at, id), happened_at) AS gap_minutes
FROM events WHERE user_id = 1 AND happened_at::DATE = DATE '2026-01-05'
ORDER BY happened_at, id;

The first gap is NULL because there is no earlier event. The 09:45 click is 30 minutes after the enquiry, so it stays. The 10:20 click is 35 minutes after that, so it starts a new session. Comparing every event to 09:00 would answer a different question.

Mark starts, then count them

We calculate the previous timestamp separately for each user. Each session start contributes a one; all other events contribute a zero. A running sum turns those markers into session numbers.

WITH gaps AS (
 SELECT *, lag(happened_at) OVER (
   PARTITION BY user_id ORDER BY happened_at, id
 ) AS previous_at FROM events
), starts AS (
 SELECT *, CASE WHEN previous_at IS NULL
   OR happened_at - previous_at > INTERVAL 30 MINUTE
   THEN 1 ELSE 0 END AS new_session
 FROM gaps
)
SELECT id, user_id, happened_at, event,
 sum(new_session) OVER (
   PARTITION BY user_id ORDER BY happened_at, id
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 )::INTEGER AS session_no
FROM starts
ORDER BY user_id, happened_at, id;

The two CTEs keep each step inspectable. PARTITION BY user_id prevents one person’s events from affecting another’s. Ordering by timestamp and unique event ID makes ties deterministic. Equal timestamps have a zero gap under this policy.

The explicit ROWS frame accumulates through the current event. The identifier is the pair (user_id, session_no): session 1 for Ada is different from session 1 for Ben. The setup also saves this query as the session_events view so later examples can use it independently.

Give the sessions meaning

Now summarize Ada’s first day:

SELECT session_no, count(*) AS events,
  min(happened_at)::TIME::VARCHAR AS start_time,
  date_diff('minute', min(happened_at), max(happened_at)) AS observed_minutes,
  bool_or(event IN ('run','enquiry')) AS engaged
FROM session_events
WHERE user_id = 1 AND happened_at::DATE = DATE '2026-01-05'
GROUP BY session_no ORDER BY session_no;

Her first session has five events and 45 minutes between its first and last recorded events. The second has one event and zero observed minutes. Zero means we have no later timestamp to measure against; it does not mean she left instantly.

Our engagement flag means “at least one run or enquiry.” Repeated runs remain events but do not multiply engaged sessions. Different products may need a different engagement definition.

SELECT session_no FROM session_events WHERE user_id = 1 GROUP BY session_no HAVING count(*) = 1 ORDER BY session_no;

Keep the definition reproducible

Late-arriving events can bridge an apparent gap and change historical session numbers. If these identifiers leave your analysis, choose a stable assignment and backfill policy instead of treating a recalculated row number as a permanent business key. Deduplicate retransmitted event IDs before sessionizing; they otherwise inflate event counts.

A useful next step is building an ordered conversion funnel from these same sessions. That lesson distinguishes “all three events happened” from “the visitor followed the intended sequence.”