User Session Reconstruction from Event Stream
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a UserEvents table of timestamped events. A new session begins whenever the gap between two consecutive events for the same user exceeds 30 minutes (1800 seconds). The very first event for each user always starts session 1. For each session, compute: user_id, session_num (1-based per user), session_start (earliest event_time), session_end (latest event_time), event_count, and duration_seconds (session_end minus session_start in seconds). Return results ordered by user_id, then session_num. Table: UserEvents
| Column Name | Type | Description |
|---|---|---|
| event_id | INT | Primary key |
| user_id | INT | User identifier |
| event_time | DATETIME | Timestamp of event (ISO format) |
Database Schema (Inferred)
UserEvents
| Column Name | Example Value |
|---|---|
| event_id | 1 |
| user_id | 1 |
| event_time | 2024-01-01 09:00:00 |
Example
UserEvents
| event_id | user_id | event_time |
|---|---|---|
| 1 | 1 | 2024-01-01 09:00:00 |
| 2 | 1 | 2024-01-01 09:10:00 |
| 3 | 1 | 2024-01-01 09:25:00 |
| 4 | 1 | 2024-01-01 10:10:00 |
| 5 | 1 | 2024-01-01 10:20:00 |
| 6 | 2 | 2024-01-01 08:00:00 |
| 7 | 2 | 2024-01-01 08:40:00 |
| 8 | 2 | 2024-01-01 08:50:00 |
Output
| user_id | session_num | session_start | session_end | event_count | duration_seconds |
|---|---|---|---|---|---|
| 1 | 1 | 2024-01-01 09:00:00 | 2024-01-01 09:25:00 | 3 | 1500 |
| 1 | 2 | 2024-01-01 10:10:00 | 2024-01-01 10:20:00 | 2 | 600 |
| 2 | 1 | 2024-01-01 08:00:00 | 2024-01-01 08:00:00 | 1 | 0 |
| 2 | 2 | 2024-01-01 08:40:00 | 2024-01-01 08:50:00 | 2 | 600 |
Explanation:
Use LAG to compare each event to the previous one per user. Mark a new session when the gap > 1800s or there is no previous event. Use SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) as a session counter. Aggregate per user+session.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.
UserEvents
| event_id | user_id | event_time |
|---|---|---|
| 1 | 1 | 2024-01-01 09:00:00 |
| 2 | 1 | 2024-01-01 09:10:00 |
| 3 | 1 | 2024-01-01 09:25:00 |
| 4 | 1 | 2024-01-01 10:10:00 |
| 5 | 1 | 2024-01-01 10:20:00 |
| 6 | 2 | 2024-01-01 08:00:00 |
| 7 | 2 | 2024-01-01 08:40:00 |
| 8 | 2 | 2024-01-01 08:50:00 |
Output
| user_id | session_num | session_start | session_end | event_count | duration_seconds |
|---|---|---|---|---|---|
| 1 | 1 | 2024-01-01 09:00:00 | 2024-01-01 09:25:00 | 3 | 1500 |
| 1 | 2 | 2024-01-01 10:10:00 | 2024-01-01 10:20:00 | 2 | 600 |
| 2 | 1 | 2024-01-01 08:00:00 | 2024-01-01 08:00:00 | 1 | 0 |
| 2 | 2 | 2024-01-01 08:40:00 | 2024-01-01 08:50:00 | 2 | 600 |