Session count and length per user

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 16 minutes. Part of the Pro drill bank.

app_events has 1 row per event: user_id and event_time. A session is a run of one user's events where each event is at most 30 minutes after the one before it. A gap of more than 30 minutes starts a new session. The length of a session is the time from its first event to its last, in whole minutes, so a session with 1 event lasts 0 minutes. For each user return: sessions: how many sessions the user had longest_minutes: the length of their longest session avg_minutes: the average session length, rounded to 1 decimal Order by user_id.

Requirements

  • Return the columns user_id, sessions, longest_minutes, avg_minutes.
  • Order by user_id.

Constraints

  • event_time has 1-minute precision.
  • A user's events are not stored in time order.
  • A gap of exactly 30 minutes does not start a new session.

Examples

Input: app_events user_id | event_time A | 2024-03-01 08:00:00 A | 2024-03-01 08:10:00 A | 2024-03-01 08:40:00 A | 2024-03-01 09:30:00 A | 2024-03-01 09:35:00 A | 2024-03-02 23:50:00 A | 2024-03-03 00:10:00 B | 2024-03-01 12:00:00 C | 2024-03-01 10:00:00 C | 2024-03-01 10:31:00 C | 2024-03-01 10:32:00 Output: user_id | sessions | longest_minutes | avg_minutes A | 3 | 40 | 21.7 B | 1 | 0 | 0 C | 2 | 1 | 0.5 Why this passes: A has three sessions: 08:00 to 08:40 (40 minutes, because 08:10 to 08:40 is exactly 30), 09:30 to 09:35 (5) and the one that crosses midnight (20). B has one single-event session. C splits at 31 minutes into a 0-minute and a 1-minute session.

Input: app_events user_id | event_time X | 2024-04-01 09:00:00 X | 2024-04-01 09:20:00 Y | 2024-04-01 09:05:00 Y | 2024-04-01 11:00:00 Output: user_id | sessions | longest_minutes | avg_minutes X | 1 | 20 | 20 Y | 2 | 0 | 0 Why this passes: X has one 20-minute session. Y has two single-event sessions, so its longest and average length are 0.

Topics: lakebench, sql, sessionization, gaps, summary.

More SQL interview questions · All interview problems · Learn data engineering

intermediate

Session count and length per user

Interview-style drill: Split each user's events into sessions, then report how many sessions they had and how long they lasted.

`app_events` has 1 row per event: `user_id` and `event_time`. A session is a run of one user's events where each event is at most 30 minutes after the one before it. A gap of more than 30 minutes starts a new session. The length of a session is the time from its first event to its last, in whole minutes, so a session with 1 event lasts 0 minutes. For each user return: - `sessions`: how many sessions the user had - `longest_minutes`: the length of their longest session - `avg_minutes`: the average session length, rounded to 1 decimal Order by `user_id`.