Product analytics teams segment user activity into sessions to measure engagement depth — how many pages a user visits per visit, how long they stay, and where they drop off. A common heuristic defines a new session when a user has been inactive for more than 30 minutes. Using the events table, return user_id, session_num, session_start, session_end, and event_count for each session, ordered by user_id then session_num.
events
| column | type |
|---|---|
| id | INTEGER |
| user_id | INTEGER |
| event_time | TIMESTAMP |
events
| id | user_id | event_time |
|---|---|---|
| 1 | 1 | 2024-01-01 10:00:00 |
| 2 | 1 | 2024-01-01 10:15:00 |
| 3 | 1 | 2024-01-01 10:50:00 |
| 4 | 1 | 2024-01-01 11:00:00 |
| 5 | 2 | 2024-01-01 09:00:00 |
| 6 | 2 | 2024-01-01 09:20:00 |
| user_id | session_num | session_start | session_end | event_count |
|---|---|---|---|---|
| 1 | 1 | 2024-01-01 10:00:00 | 2024-01-01 10:15:00 | 2 |
| 1 | 2 | 2024-01-01 10:50:00 | 2024-01-01 11:00:00 | 2 |
| 2 | 1 | 2024-01-01 09:00:00 | 2024-01-01 09:20:00 | 2 |
User 1's gap between 10:15 and 10:50 is 35 minutes, which exceeds the 30-minute threshold — two sessions. User 2's events are only 20 minutes apart — one session.