App Sessions From the Event Stream

Hard
447.

App Sessions From the Event Stream

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

ColumnType
event_idint
user_idint
screenvarchar
event_timedatetime

Primary key: event_id.

One row per screen view. Two events of a user can share a timestamp.

Example

The rows must come back in the order shown.

app_events

event_iduser_idscreenevent_time
11feed2025-10-01 09:00:00
21reels2025-10-01 09:12:00
31profile2025-10-01 09:42:00
41feed2025-10-01 10:12:01
51dm_inbox2025-10-01 10:20:30
62feed2025-10-01 21:00:00
72search2025-10-01 23:00:00
82reels2025-10-01 23:00:00

Output

user_idsessionslongest_session_minutesavg_events_per_session
12422.5
2201.5
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
event_idint
user_idint
screenvarchar
event_timedatetime

Primary key: event_id.

One row per screen view. Two events of a user can share a timestamp.

Examples

Example 1

app_events

event_iduser_idscreenevent_time
11feed2025-10-01 09:00:00
21reels2025-10-01 09:12:00
31profile2025-10-01 09:42:00
41feed2025-10-01 10:12:01
51dm_inbox2025-10-01 10:20:30
62feed2025-10-01 21:00:00
72search2025-10-01 23:00:00
82reels2025-10-01 23:00:00

Output

user_idsessionslongest_session_minutesavg_events_per_session
12422.5
2201.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_id

Another 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 →