21. Sessionize events (30-minute gap)
Asked in screens shaped like: Netflix, Spotify, Airbnb
Group each customer’s events into sessions that break after 30 minutes of inactivity. This is the three-step gap-and-island pattern: look back, flag a break, then accumulate the flag into an id.
Requirements
- Columns: customer_id, occurred_at, session_id
- session_id restarts at 1 for every customer
- A new session starts on the first event or when the gap from the previous event exceeds 30 minutes
- Order by customer_id, then occurred_at
Expected output
customer_id, occurred_at, session_id
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
events
~400 rows
Clickstream. payload is raw JSON — use json_extract on it.
- event_idINTEGER
- customer_idTEXT
- event_nameTEXT— page_view, add_cart, purchase, search
- occurred_atTEXT
- payloadTEXT— {"source":"web|ios","campaign":"c0…c4"}
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
LAG(occurred_at) per customer gives you the previous timestamp.
Hint 2
(JULIANDAY(a) - JULIANDAY(b)) * 24 * 60 converts a date difference to minutes in SQLite.
Hint 3
A running SUM over the 0/1 break flag becomes the session id.
WITH ordered AS (
SELECT customer_id, occurred_at,
LAG(occurred_at) OVER (PARTITION BY customer_id ORDER BY occurred_at) AS prev_at
FROM events
),
flagged AS (
SELECT customer_id, occurred_at,
CASE
WHEN prev_at IS NULL THEN 1
WHEN (JULIANDAY(occurred_at) - JULIANDAY(prev_at)) * 24 * 60 > 30 THEN 1
ELSE 0
END AS new_sess
FROM ordered
)
SELECT customer_id, occurred_at,
SUM(new_sess) OVER (PARTITION BY customer_id ORDER BY occurred_at) AS session_id
FROM flagged
ORDER BY customer_id, occurred_at;Why it works
Because the first event is flagged 1, the running sum starts every customer at session 1 with no extra offset. The same three-step shape solves streaks, outage windows, and any other gap-and-island question.
Run your query to see the result set.
Submit to run the checks.
Why it works
Because the first event is flagged 1, the running sum starts every customer at session 1 with no extra offset. The same three-step shape solves streaks, outage windows, and any other gap-and-island question.
Pro problem
Hard problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems