User Sessions From Thirty-Minute Event Gaps — SQL Problem
The web app does not log sessions, so analytics cuts them from the event stream: a user's first event starts a session, and so does any event that comes…
- Difficulty: Hard
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #416
Problem statement
The web app does not log sessions, so analytics cuts them from the event stream: a user's first event starts a session, and so does any event that comes more than 30 minutes after that user's previous event. A gap of exactly 30 minutes stays in the same session. A session's length is the whole minutes from its first to its last event (TIMESTAMPDIFF(MINUTE, …)), so a one-event session lasts 0.
Return user_id, sessions (how many sessions the user had) and longest_session_minutes for every user with events, ordered by user_id.
Tables
Table: events
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| event_name | varchar |
| occurred_at | datetime |
Primary key: event_id.
One row per event; a user never has two events at the same second.
Examples
Example 1
events
| event_id | user_id | event_name | occurred_at |
|---|---|---|---|
| 1 | 1 | login | 2024-07-01 09:00:00 |
| 2 | 1 | task_added | 2024-07-01 09:20:00 |
| 3 | 1 | comment_posted | 2024-07-01 09:50:00 |
| 4 | 1 | login | 2024-07-01 10:30:00 |
| 5 | 1 | report_viewed | 2024-07-01 10:45:00 |
| 6 | 2 | login | 2024-07-01 14:00:00 |
| 7 | 2 | login | 2024-07-01 14:31:00 |
| 8 | 3 | login | 2024-07-02 08:00:00 |
| 9 | 3 | file_uploaded | 2024-07-02 08:29:30 |
| 10 | 3 | task_added | 2024-07-02 08:59:00 |
Output
| user_id | sessions | longest_session_minutes |
|---|---|---|
| 1 | 2 | 50 |
| 2 | 2 | 0 |
| 3 | 1 | 59 |
How to solve User Sessions From Thirty-Minute Event Gaps
This is sessionisation, a gaps-and-islands problem on timestamps. Step one marks the events that start a session: with LAG(occurred_at) over each user's events in time order, an event starts a session when there is no previous event (LAG is NULL) or the gap is more than 1,800 seconds. Measuring in seconds matters — TIMESTAMPDIFF(MINUTE, …) truncates, so a gap of 30 minutes 30 seconds would read as 30 and wrongly stay in the session.
Step two turns the flags into session numbers: a running sum of is_start in the same order gives every event the count of starts at or before it, which is the same number for all events of one session. The frame is written as ROWS with a unique order so peers can never be summed together.
Step three groups by (user, session number) to get each session's length from its first to its last event, and a final group by user counts sessions and takes the longest. In the example user 1's 09:50 event is exactly 30 minutes after 09:20 and stays in the first session (50 minutes long); 10:30 is 40 minutes later and starts the second.
The alternative avoids windows: a start is an event with no earlier event of the same user within 30 minutes (NOT EXISTS), and every event belongs to the latest start at or before it. It is quadratic per user; the window plan is one sort.
Reference solution (MySQL)
WITH flagged AS (
SELECT user_id, event_id, occurred_at,
CASE WHEN LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at, event_id) IS NULL
OR TIMESTAMPDIFF(SECOND, LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at, event_id), occurred_at) > 1800
THEN 1 ELSE 0 END AS is_start
FROM events
), numbered AS (
SELECT user_id, occurred_at,
SUM(is_start) OVER (PARTITION BY user_id ORDER BY occurred_at, event_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM flagged
), sessions AS (
SELECT user_id, session_no, TIMESTAMPDIFF(MINUTE, MIN(occurred_at), MAX(occurred_at)) AS minutes
FROM numbered
GROUP BY user_id, session_no
)
SELECT user_id, COUNT(*) AS sessions, MAX(minutes) AS longest_session_minutes
FROM sessions
GROUP BY user_id
ORDER BY user_idAnother way
WITH starts AS (
SELECT e.user_id, e.occurred_at FROM events e
WHERE NOT EXISTS (SELECT 1 FROM events p WHERE p.user_id = e.user_id AND p.occurred_at < e.occurred_at
AND TIMESTAMPDIFF(SECOND, p.occurred_at, e.occurred_at) <= 1800)
), tagged AS (
SELECT e.user_id, e.occurred_at,
(SELECT MAX(s.occurred_at) FROM starts s WHERE s.user_id = e.user_id AND s.occurred_at <= e.occurred_at) AS session_start
FROM events e
), sessions AS (
SELECT user_id, session_start, TIMESTAMPDIFF(MINUTE, session_start, MAX(occurred_at)) AS minutes
FROM tagged GROUP BY user_id, session_start
)
SELECT user_id, COUNT(*) AS sessions, MAX(minutes) AS longest_session_minutes
FROM sessions GROUP BY user_id ORDER BY user_id← Seat-Based Monthly Bill for Every Workspace · Onboarding Funnel With Steps Taken in Order →