Identify User Sessions from Page View Events
Preview mode. Log in to edit, run, submit, and save progress.
Description
You are given a PageViews table. A new session begins when the gap between two consecutive page views for the same user exceeds 30 minutes (1800 seconds). Write a SQL query to identify all sessions per user. For each session return: user_id, a sequential session_id (starting at 1 per user), the session_start timestamp, the session_end timestamp, and the page_views count. Return the result ordered by user_id, then session_id. Table: PageViews
| Column Name | Type | Description |
|---|---|---|
| id | INT | Primary key |
| user_id | INT | ID of the user |
| viewed_at | DATETIME | Timestamp of the page view |
Database Schema (Inferred)
PageViews
| Column Name | Example Value |
|---|---|
| id | 1 |
| user_id | 1 |
| viewed_at | 2023-01-01 10:00:00 |
Example
PageViews
| id | user_id | viewed_at |
|---|---|---|
| 1 | 1 | 2023-01-01 10:00:00 |
| 2 | 1 | 2023-01-01 10:10:00 |
| 3 | 1 | 2023-01-01 10:25:00 |
| 4 | 1 | 2023-01-01 11:10:00 |
| 5 | 1 | 2023-01-01 11:20:00 |
| 6 | 2 | 2023-01-01 09:00:00 |
| 7 | 2 | 2023-01-01 09:35:00 |
Output
| user_id | session_id | session_start | session_end | page_views |
|---|---|---|---|---|
| 1 | 1 | 2023-01-01 10:00:00 | 2023-01-01 10:25:00 | 3 |
| 1 | 2 | 2023-01-01 11:10:00 | 2023-01-01 11:20:00 | 2 |
| 2 | 1 | 2023-01-01 09:00:00 | 2023-01-01 09:00:00 | 1 |
| 2 | 2 | 2023-01-01 09:35:00 | 2023-01-01 09:35:00 | 1 |
Explanation:
User 1: gap between 10:25 and 11:10 is 45 min > 30 min → new session. User 2: gap between 09:00 and 09:35 is 35 min > 30 min → new 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.
PageViews
| id | user_id | viewed_at |
|---|---|---|
| 1 | 1 | 2023-01-01 10:00:00 |
| 2 | 1 | 2023-01-01 10:10:00 |
| 3 | 1 | 2023-01-01 10:25:00 |
| 4 | 1 | 2023-01-01 11:10:00 |
| 5 | 1 | 2023-01-01 11:20:00 |
| 6 | 2 | 2023-01-01 09:00:00 |
| 7 | 2 | 2023-01-01 09:35:00 |
Output
| user_id | session_id | session_start | session_end | page_views |
|---|---|---|---|---|
| 1 | 1 | 2023-01-01 10:00:00 | 2023-01-01 10:25:00 | 3 |
| 1 | 2 | 2023-01-01 11:10:00 | 2023-01-01 11:20:00 | 2 |
| 2 | 1 | 2023-01-01 09:00:00 | 2023-01-01 09:00:00 | 1 |
| 2 | 2 | 2023-01-01 09:35:00 | 2023-01-01 09:35:00 | 1 |