App Sessions From the Event Stream — SQL Problem
The app logs an event for every screen a user opens. Analytics splits each user's events into sessions: events are taken in order of event_time, then…
- Difficulty: Hard
- Topics: Window Functions, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #447
Problem statement
The app logs an event for every screen a user opens. Analytics splits each user's events into sessions: events are taken in order of event_time, then event_id, and a new session starts at a user's first event and at every event that comes more than 30 minutes (1800 seconds) after the user's previous event. A gap of exactly 30 minutes stays in the same session.
For every user with events, return the columns user_id, sessions, longest_session_minutes (the largest whole-minute span, TIMESTAMPDIFF(MINUTE, …), from a session's first to its last event; a one-event session is 0) and avg_events_per_session (rounded to 1 decimal), ordered by user_id.
Tables
Table: app_events
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| screen | varchar |
| event_time | datetime |
Primary key: event_id.
One row per screen view. Two events of a user can share a timestamp.
Examples
Example 1
app_events
| event_id | user_id | screen | event_time |
|---|---|---|---|
| 1 | 1 | feed | 2025-10-01 09:00:00 |
| 2 | 1 | reels | 2025-10-01 09:12:00 |
| 3 | 1 | profile | 2025-10-01 09:42:00 |
| 4 | 1 | feed | 2025-10-01 10:12:01 |
| 5 | 1 | dm_inbox | 2025-10-01 10:20:30 |
| 6 | 2 | feed | 2025-10-01 21:00:00 |
| 7 | 2 | search | 2025-10-01 23:00:00 |
| 8 | 2 | reels | 2025-10-01 23:00:00 |
Output
| user_id | sessions | longest_session_minutes | avg_events_per_session |
|---|---|---|---|
| 1 | 2 | 42 | 2.5 |
| 2 | 2 | 0 | 1.5 |
How to solve App Sessions From the Event Stream
Sessionisation is gaps and islands on time. First, compare every event with the user's previous one: LAG(event_time) over a window partitioned by user and ordered by event_time, event_id. An event starts a session when there is no previous event or TIMESTAMPDIFF(SECOND, prev, cur) > 1800 — strictly more, so a gap of exactly 30 minutes continues the session.
Second, a running SUM of the start flags, with an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame, gives every event its session number; the default RANGE frame would treat events with the same timestamp as peers and could hand them a later number. Grouping by (user_id, session_no) gives each session's first and last time and its event count, and a final GROUP BY user counts sessions, takes the longest span in whole minutes and averages events per session.
The alternative identifies session starts directly — an event with no earlier event of the same user within 1800 seconds (since the immediate predecessor is the latest earlier event, this is the same test) — then tags every event with the latest start at or before it via a correlated MAX. It is correct but quadratic per user; the window version is one sort.
Reference solution (MySQL)
WITH gaps AS (
SELECT user_id, event_id, event_time,
CASE WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time, event_id) IS NULL
OR TIMESTAMPDIFF(SECOND, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time, event_id), event_time) > 1800
THEN 1 ELSE 0 END AS starts_session
FROM app_events
), numbered AS (
SELECT user_id, event_time,
SUM(starts_session) OVER (PARTITION BY user_id ORDER BY event_time, event_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_no
FROM gaps
), sessions AS (
SELECT user_id, session_no, MIN(event_time) AS started, MAX(event_time) AS ended, COUNT(*) AS events
FROM numbered GROUP BY user_id, session_no
)
SELECT user_id,
COUNT(*) AS sessions,
MAX(TIMESTAMPDIFF(MINUTE, started, ended)) AS longest_session_minutes,
ROUND(AVG(events), 1) AS avg_events_per_session
FROM sessions
GROUP BY user_id
ORDER BY user_idAnother way
WITH starts AS (
SELECT a.user_id, a.event_time FROM app_events a
WHERE NOT EXISTS (
SELECT 1 FROM app_events b
WHERE b.user_id = a.user_id
AND (b.event_time < a.event_time OR (b.event_time = a.event_time AND b.event_id < a.event_id))
AND TIMESTAMPDIFF(SECOND, b.event_time, a.event_time) <= 1800)
), tagged AS (
SELECT e.user_id, e.event_time,
(SELECT MAX(s.event_time) FROM starts s WHERE s.user_id = e.user_id AND s.event_time <= e.event_time) AS started
FROM app_events e
), sessions AS (
SELECT user_id, started, MAX(event_time) AS ended, COUNT(*) AS events FROM tagged GROUP BY user_id, started
)
SELECT user_id, COUNT(*) AS sessions, MAX(TIMESTAMPDIFF(MINUTE, started, ended)) AS longest_session_minutes,
ROUND(AVG(events), 1) AS avg_events_per_session
FROM sessions GROUP BY user_id ORDER BY user_id← Median Likes per Creator · T20 Innings Scored at a Strike Rate Above 150 →