Event Analytics: From Rows to Sessions and Snapshots

A support engineer asks: "When was this user active?" and "What state was this device in at midnight?" Both start with events, but their output grains differ. Sessionization combines events into one row. A snapshot chooses one eligible event per device. Define the grain before choosing SQL syntax.

Preparation and outcomes

You should know SELECT, WHERE, GROUP BY, COUNT, and timestamp subtraction. A CTE (WITH name AS (...)) names an intermediate query for inspection. A window function preserves input rows while calculating across related rows; a grouped aggregate collapses them.

You will trace session boundaries, place cutoff filters correctly, and handle ties. Complete Event Sessions, then Latest Device State, then the event checkpoint.

1. Derive the session state

One output row is one (user_id, session_index) session. The first event starts a session; a gap strictly greater than 30 minutes starts another. Compare adjacent events, not every event with the session start.

For one user, predict the final two columns before reading them:

event_id	time	gap from previous	is_new	session_index
11	09:00	none	1	1
12	09:30	30 minutes	0	1
13	10:00	30 minutes	0	1
14	10:31	31 minutes	1	2
15	10:31	0 minutes	0	2

The first session lasts an hour: a chain of short gaps can form a long session. Duplicate timestamps still represent separate events and both count. The second session has two events and equal start/end times. Restart the index at 1 for another user; this is the purpose of PARTITION BY user_id.

2. Build inspectable stages

Keep event_id through each window stage. Use the same unique ascending (event_ts, event_id) order in both windows so tied rows stay consistent.

First attach the preceding timestamp:

LAG(event_ts) OVER (
  PARTITION BY user_id ORDER BY event_ts, event_id
) AS previous_ts

In a second CTE, compute a boundary flag:

CASE
  WHEN previous_ts IS NULL THEN 1
  WHEN event_ts - previous_ts > INTERVAL '30 minutes' THEN 1
  ELSE 0
END AS is_new

In a third stage, number sessions using the prefix sum of flags:

SUM(is_new) OVER (
  PARTITION BY user_id ORDER BY event_ts, event_id
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS session_index

The invariant: the running total is the number of session starts seen for that user. ROWS makes the frame a row-by-row prefix. Without the shared tie breaker, the two windows can disagree about which tied event came first. An outer sort changes presentation, not window computation. Named stages also avoid trying to nest one window call directly inside another.

Only now group by (user_id, session_index) and compute the minimum timestamp, maximum timestamp, and count. Grouping before detecting gaps loses the sequence. Inspect each stage on the table above: incorrect flags indicate a gap/order/partition problem; correct flags but wrong totals suggest grouping.

3. Select a coherent as-of row

Now one output row is one device with an event at or before the cutoff. Every output attribute must come from the same selected event.

event_id	recorded_at (UTC)	status
21	May 31, 23:50	offline
22	June 1, 00:00	online
23	June 1, 00:00	error
24	June 1, 00:01	online

With the inclusive June 1 midnight cutoff, remove event 24 before ranking. Events 22 and 23 tie on time. This course explicitly chooses the larger event_id, so event 23 wins. This policy makes the answer reproducible; an ID does not establish which physical event really occurred later.

ROW_NUMBER() OVER (
  PARTITION BY device_id
  ORDER BY recorded_at DESC, event_id DESC
) AS rn

Put recorded_at <= TIMESTAMPTZ '2025-06-01 00:00:00+00' in the input query; select rn = 1 in an outer query. Ranking the whole history first gives event 24 rank 1. Removing it later leaves no rank-1 row for the device instead of falling back to event 23.

Independent MAX(status), MAX(customer_id), and MAX(recorded_at) can combine values from different events into a row that never existed. Select a complete ranked row instead. A device with only future events is absent: this task has no separate device master table to preserve.

4. Reconstruct before submitting

1. For times 08:00, 08:30, 09:01, 09:01, how many sessions and events per session?
2. Why can final ORDER BY device_id not resolve ties inside a device?
3. What changes if the cutoff becomes exclusive?

Answers: (1) Two sessions of two events; only the 31-minute gap splits. (2) Row selection happens inside the window and needs its own tie breaker. (3) The input comparison becomes <; recency order stays the same.

The next module adds empty entities and repeated graph paths to this same habit: define the output row, then trace how input rows become that result.

Further reading

PostgreSQL's window tutorial explains partitions, frames, and filtering before windows. The event-ID policy above is an explicit contract of these exercises.