Data Analyst Interview
Menu
Browse in your language. All mock interviews, preparation sessions and feedback are in English only.

How to Turn Events Into Sessions in SQL

Compare each event with the preceding event for the same customer. Mark a new session when the gap reaches your inactivity threshold, then take a running sum of those marks. With a 30-minute threshold, a visit can cross midnight or last longer than 30 minutes while activity continues.

What exactly starts a session?

Suppose a retailer wants to group interactions for identified customers across its US and UK stores. For this exercise the first observed event starts a session, and a gap of exactly 30 minutes starts another. This is a custom metric. Use the pair of customer ID and session number as the session key because each customer’s numbering begins at one.

Try the boundary cases in SQLite

The synthetic timestamps are elapsed whole minutes from a common UTC midnight. Minute 1425 is 23:45, and minute 1445 is 00:05 the next day. Each event has a unique ID that breaks timestamp ties. Both customers have complete histories in the example.

WITH events(customer,event_id,minute_utc) AS (
  VALUES ('US-A',1,1425), ('US-A',2,1445),
         ('US-A',3,1475), ('US-A',4,1506),
         ('UK-B',5,1445), ('UK-B',6,1445)
), gaps AS (
  SELECT *, LAG(minute_utc) OVER (
    PARTITION BY customer ORDER BY minute_utc,event_id
  ) AS prev FROM events
), flags AS (
  SELECT *, CASE WHEN prev IS NULL
    OR minute_utc-prev >= 30 THEN 1 ELSE 0 END AS starts
  FROM gaps
)
SELECT customer,event_id,minute_utc,
  SUM(starts) OVER (PARTITION BY customer
    ORDER BY minute_utc,event_id
    ROWS UNBOUNDED PRECEDING) AS session_no
FROM flags ORDER BY customer,minute_utc,event_id;

Read the six events by customer

US-A receives session numbers 1, 1, 2 and 3. The first two events cross midnight with only a 20-minute gap. The third event starts session 2 exactly at the boundary. A further gap of 31 minutes starts session 3. UK-B receives 1 and 1. Its simultaneous events both belong to one session.

The result has six events, two identified customers and four sessions. Counting distinct session numbers alone gives three because both customers have a session numbered 1. To count sessions directly, sum the starts column in flags.

What changes in a daily dashboard?

For a report starting at midnight, read the preceding event before assigning the first in-range event its flag. Otherwise US-A’s 00:05 event appears to start a session. Decide whether the daily report counts sessions starting that day or sessions active that day. The cross-midnight session appears on two dates under the second definition. For a stable session start or number, carry prior session state or compute over the relevant history before filtering.

Real timestamps often have second or finer precision. Compare elapsed time at the source precision before rounding. Deduplicate retransmitted event IDs, audit unknown customer IDs separately, and recompute affected sessions when a late event bridges a gap. The event ordering, partition and explicit row frame used here are documented in the SQLite window functions reference.

For related exercises, see running totals with tied timestamps and counting distinct users across reporting periods.

Leave a Comment

✉ WhatsApp