Sessionization
Turn an event stream into sessions — the gap-and-islands pattern every analyst eventually writes, built step by step with window functions.
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.
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.
Use AVG() with a ROWS frame specifying 6 PRECEDING to current row — this captures exactly 7 physical rows regardless of date gaps. RANGE with INTERVAL '6 days' PRECEDING is the alternative when you need a true calendar window, but it requires at most one row per date and may include fewer than 7 rows if days are missing.
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.