Sessionization
Turn an event stream into sessions — the gap-and-islands pattern every analyst eventually writes, built step by step with window functions.
Written and reviewed by Shreyash Prashu Last reviewed
What you'll learn
- Why sessionization is the gap-and-islands problem
- Using LAG() to find the time since the previous event
- Marking session boundaries, then numbering sessions with a running sum
- Why the gap threshold is a product decision, not a SQL one
Before you start
A stream of events — clicks, orders, log lines — says little on its own. You want to group it: which events belong to the same visit? That is sessionization, and the SQL pattern behind it is gaps and islands — find the gaps (pauses longer than a threshold), and the runs of events between them are the islands, each one a session.
What counts as a gap is yours to define: a website usually calls 30 minutes of inactivity a new session; a B2B tool might use 24 hours. We will use a 7-day threshold for a user’s orders. Watch one user whose four orders fall into two shopping runs:
The build, in one query
Four moves do it. LAG gets the previous order’s date, a subtraction gives the gap in days, a CASE flags each row that starts a new session (a first order, or a gap over 7 days), and a running sum of that flag becomes the session number — it ticks up only at boundaries and stays flat within a session:
WITH gaps AS (
SELECT id, order_date,
CAST(julianday(order_date)
- julianday(LAG(order_date) OVER (ORDER BY order_date)) AS INT) AS days_since_prev
FROM orders
),
flagged AS (
SELECT id, order_date,
CASE WHEN days_since_prev IS NULL OR days_since_prev > 7 THEN 1 ELSE 0 END AS is_new_session,
days_since_prev
FROM gaps
)
SELECT id, order_date, days_since_prev, is_new_session,
SUM(is_new_session) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS session_id
FROM flagged;| id | order_date | days_since_prev | is_new_session | session_id |
|---|---|---|---|---|
| 1 | 2024-01-01 | NULL | 1 | 1 |
| 2 | 2024-01-04 | 3 | 0 | 1 |
| 3 | 2024-01-20 | 16 | 1 | 2 |
| 4 | 2024-01-22 | 2 | 0 | 2 |
Read the running sum: it is 1 for the first two orders (one boundary so far), then jumps to 2 at order 3 where the 16-day gap tripped the flag. Every order now carries a session_id, so a final GROUP BY session_id rolls each island up to one row:
| session_id | start | end | orders | span_days |
|---|---|---|---|---|
| 1 | 2024-01-01 | 2024-01-04 | 2 | 3 |
| 2 | 2024-01-20 | 2024-01-22 | 2 | 2 |
That is the artifact a stakeholder wants: when each session started and ended, how many orders, how long it spanned. (Per user, you simply add PARTITION BY user_id to every window.)
Practice
Quick check
Practice this in an interview
All questionsGaps-and-islands is the problem of identifying contiguous ranges (islands) within ordered sequential data and the breaks (gaps) between them. The classic solution subtracts a dense sequential integer from the ordering column — equal differences belong to the same island.
GROUP BY collapses multiple rows into one row per group and discards individual row data. PARTITION BY divides rows into groups for the window function calculation but preserves every row in the output — each row retains its own columns plus the computed window value.
Use SUM() OVER (ORDER BY ...) — the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This works correctly for strictly increasing order columns, but silently over-counts when multiple rows share the same ORDER BY value because the RANGE default includes all peers.
Batch pipelines process data in bounded chunks on a schedule — simple to build and test, but latency is measured in hours or days. Streaming pipelines process records continuously as they arrive — latency drops to seconds or milliseconds, but correctness requires handling late arrivals, watermarks, and stateful aggregations. Choose streaming when business decisions need fresh data; choose batch when daily freshness is acceptable and operational simplicity matters.