SQL Interview: Sessionize User Events with a 30-Minute Inactivity Gap

Problem and Applicable Context

Assume this PostgreSQL event table:

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  occurred_at timestamptz NOT NULL
);

Order each user's events by occurredat and eventid. The first event opens a session. Every later event opens a new session when its gap from the previous event is greater than or equal to 30 minutes. Return userid, a one-based sessionseq, sessionstart, sessionend, event_count, and session_duration. An exact 30-minute gap starts a new session; that boundary is part of the contract.

This pattern appears in clickstream processing, product analytics, and behavioral funnels. It is not the same problem as finding consecutive login days. A daily streak compares adjacent calendar dates; sessionization compares elapsed time between adjacent events for one user. The main solution computes an exact result over a complete, logically deduplicated event set. Bounded queries and incremental materialization require extra boundary rules.

What the Interviewer Is Evaluating

The first signal is whether the candidate turns prose into an executable contract. > 30 minutes and >= 30 minutes assign the boundary event differently. Comparing with the previous event and comparing with the first event in a session are also different definitions. This prompt uses adjacent gaps, so a continuously active session may last far longer than 30 minutes.

The second signal is decomposition into three window stages: use LAG() to read the predecessor, mark session boundaries, and run a cumulative SUM() over those marks. The required window calculations cannot simply be nested arbitrarily in one PostgreSQL expression. Separate CTEs also expose each intermediate relation for inspection.

The third signal is deterministic ordering. Two events may share an occurred_at. Ordering by time alone leaves their relative order unspecified. Adding the unique event_id gives LAG() and the running sum the same total order. The elapsed time between equal timestamps is zero, so they remain in one session.

The fourth signal is time semantics. A timestamptz denotes an absolute instant suitable for elapsed- time comparison. Converting to local wall time before subtracting can inject a daylight-saving jump. Named-zone conversion belongs at presentation time, not in this session-boundary calculation.

Finally, a strong answer recognizes that late data can rewrite history. An event inserted into the middle of the timeline can connect two sessions that were previously separate. An incremental system therefore cannot treat session_seq as append-only; it needs bounded recomputation, versioned corrections, or an explicit finality watermark.

Questions to Clarify Before Answering

• Which side owns the 30-minute boundary? This prompt starts a new session at >= 30 minutes. A

strict-greater-than product rule changes one operator and all boundary expectations.

• Do we compare adjacent events or the session's first event? Adjacent events here. A maximum

session length requires separate state anchored at the session start.

• How are duplicates handled? event_id is the logical event key, so redeliveries must be

deduplicated before this query. Distinct events at the same user and timestamp remain valid rows.

• Which time zone defines the gap? Elapsed time between absolute instants. A named time zone changes

display, not the number of elapsed seconds.

• Is the query time-bounded? Full history is straightforward. A reporting range must specify

whether sessions that began before the range remain whole and must read predecessor context.

• How late can events arrive? An ad hoc query can recompute. A materialized result needs a

correction horizon, watermark, and downstream update or retraction protocol.

• What should an empty table or one-event user return? Zero rows for the empty table; one zero-

duration session for the one-event user.

30-Second Answer Framework

“Within each user, I create a deterministic order by occurredat, eventid and use LAG(occurred_at) to get the predecessor. I mark the first row and every gap of at least 30 minutes as 1, with all other rows marked 0. A cumulative sum over an explicit ROWS frame gives a one-based session sequence. I then group by user and sequence to calculate the start, end, count, and duration. Tests cover 29 minutes 59 seconds, exactly 30 minutes, tied timestamps, one-event users, duplicate IDs, late data, and the beginning of a reporting range. For incremental materialization, I recompute the affected user inside the allowed lateness window instead of assuming sessions only append.”

Step-by-Step Deep Dive

First obtain the predecessor with one shared ordering rule. event_id does not affect elapsed time; it only stabilizes equal timestamps:

WITH ordered AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    LAG(occurred_at) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
    ) AS previous_at
  FROM events
),
marked AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    CASE
      WHEN previous_at IS NULL THEN 1
      WHEN occurred_at - previous_at >= INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM ordered
),
sessionized AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    SUM(is_new_session) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_seq
  FROM marked
)
SELECT
  user_id,
  session_seq,
  MIN(occurred_at) AS session_start,
  MAX(occurred_at) AS session_end,
  COUNT(*) AS event_count,
  MAX(occurred_at) - MIN(occurred_at) AS session_duration
FROM sessionized
GROUP BY user_id, session_seq
ORDER BY user_id, session_seq;

The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW matters. The running total must absorb boundary markers one physical row at a time rather than inherit peer semantics from a default window frame. The unique event_id removes peers in this query, yet stating the frame locks in the intent and prevents a future removal of the tie-breaker from silently changing behavior.

Use these events to check the boundary:

User 1 has two sessions: two events over five minutes, then three events over 35 minutes. User 2's total span is 58 minutes, yet every adjacent gap is below 30 minutes, so all three events remain in one session. This distinguishes adjacent-gap sessionization from a maximum session duration.

Correctness follows from a simple invariant. The first row for a user raises the running sum from zero to one. After that, only a row satisfying the boundary predicate increases the sum; every non-boundary row preserves it. Two rows have the same cumulative value exactly when no marked boundary separates them. Grouping by user and that value therefore yields every maximal contiguous segment without combining users or crossing a boundary.

For N events, the window plan normally sorts by user and time, giving O(N log N) time; the window scan and aggregation are O(N). Intermediate state is O(N) and may spill. An index can match the logical order:

CREATE INDEX events_session_order_idx
  ON events (user_id, occurred_at, event_id);

The index does not guarantee a sort-free plan. Full-scan cost, visibility, parallelism, and predicates all influence the optimizer. Inspect scans, sorts, temporary I/O, estimated rows, and actual rows with EXPLAIN (ANALYZE, BUFFERS) on representative data instead of declaring success from index presence.

Time-range filtering is the easiest correctness trap. If a query begins at 10:00 while a user has events at 09:50 and 10:10, filtering first falsely marks 10:10 as a new session. If the output only needs membership inside the range, read at least the nearest predecessor before the start for each user, then exclude context rows from output. If the output must include the complete session start, continue reading backward until reaching a genuine 30-minute boundary.

Late data can also merge history. Events at 09:00 and 09:50 initially form separate sessions. A late 09:25 event changes both adjacent gaps to 25 minutes and merges them. Batch processing can recompute the affected partition. An incremental system should recompute by user over the allowed lateness horizon and publish versions or retractions. The contract must say whether data beyond the watermark is quarantined, discarded, or allowed to trigger a wider correction.

Validate each stage. Every non-first previous_at in ordered must equal the preceding timestamp in the shared order. marked contains 1 only on first rows and threshold boundaries. For each user, session_seq begins at one, never decreases, and increases by at most one. The final relation must have sessionstart <= sessionend; its event counts must sum to the number of logical input events; and the gap across adjacent sessions must be at least 30 minutes.

High-Quality Sample Answer

“I would first confirm that the boundary is greater than or equal to 30 minutes and that the comparison uses adjacent events. A unique event_id deduplicates logical events, while different events at the same time remain. The first CTE orders each user by time and event ID and uses LAG for the previous timestamp. The second marks the first row and every threshold gap. The third takes a running sum over an explicit ROWS frame to create a stable session sequence. I then aggregate by user and sequence for start, end, count, and duration.

I would test 29 minutes 59 seconds and exactly 30 minutes, equal timestamps, one-event users, and an empty table. I would also test a predecessor before the reporting range, because filtering before LAG creates a false session boundary. Sorting dominates at roughly O(N log N). An index on (userid, occurredat, event_id) may provide the needed order, but the decision comes from the buffer-aware execution plan.

For a continuously materialized result, I would not treat session numbers as immutable. Events at 09:00 and 09:50 are separate until a late 09:25 event bridges them. The system needs user-scoped recomputation inside the lateness window and version-aware consumers. Events beyond the watermark must follow an explicit quarantine or wider-correction policy.”

Common Mistakes

• Subtract the session's first event from the current event → Continuously active users are split

once the total duration exceeds 30 minutes → compare adjacent events as specified.

• Keep an exact 30-minute gap in the old session → This violates the >= 30 minutes contract →

write a dedicated threshold test.

• Order only by occurred_at → Equal timestamps lack a stable total order → **add unique

event_id and reuse the order in both windows.**

• Omit the explicit ROWS frame → Default peer behavior may differ from row-by-row accumulation →

state the frame from the first row through the current row.

• Subtract local wall times → A daylight-saving jump creates or hides an hour → **compare

timestamptz instants and localize only for display.**

• Filter at the report start before LAG → The first in-range event loses its predecessor and

becomes a false boundary → read pre-boundary context before trimming output.

• Count redeliveries as events → event_count is inflated → deduplicate by the logical event key.
• Assume historical sessions only append → Late data can move boundaries or merge sessions →

recompute a bounded range and publish correctable results.

• Assume a matching index eliminates every sort → The optimizer may choose another plan based on

scan cost and predicates → inspect a representative plan and temporary I/O.

Follow-Up Questions and Answers

Follow-up 1: What changes if an exact 30-minute gap stays in the old session?

Change the boundary predicate from >= INTERVAL '30 minutes' to > INTERVAL '30 minutes'. The rest of the window pipeline is unchanged, but the metric definition and every test fixture must change with it. Keep cases for 29:59, 30:00, and 30:01 so the policy cannot drift later.

Follow-up 2: Is the running-sum solution enough if a session may last at most two hours?

No. Adjacent small gaps can extend a session indefinitely, so the next boundary also depends on the dynamic session start. A recursive CTE, an ordered state machine, or per-user state in a stream processor is usually clearer. First distinguish a fixed two-hour window from “two hours after the session's first event”; those are different contracts.

Follow-up 3: How would you correct a materialized result for an event that arrives 24 hours late?

Locate the event by user_id, read a range spanning at least one confirmed boundary on either side of the late timestamp, recompute that segment, and diff it against the prior version. Output needs stable business keys and versions so consumers can apply updates, merges, and retractions idempotently. If the watermark forbids changing 24-hour-old output, quarantine the event and expose a data-quality signal rather than silently ignoring it.

Follow-up 4: How would you optimize this for billions of events?

Read time partitions that can be pruned and exploit (userid, occurredat, event_id) order to reduce sorting. A periodic job carries each user's last event and open-session state across partition boundaries so a file or date boundary does not become a session boundary. Validate the design with real user skew, sort spill, scanned bytes, and end-to-end latency. A single super-user hotspot may require a dedicated ordered path.

Follow-up 5: How do you prove that no event was omitted or counted twice?

Use conservation checks. The sum of final event_count values must equal the deduplicated input row count. Every eventid maps to exactly one (userid, session_seq). Session sequences begin at one and increase contiguously per user. Adjacent gaps inside a session are below 30 minutes, while boundaries between sessions are at least 30 minutes. Then run property tests with shuffled input, redeliveries, equal timestamps, partition edges, and late-event merges.