SQL interview question: User Session Duration
Calculate User Session Duration
Product analytics wants to measure average session length by calculating time between first and last event.
Detect sessions (gap > 30 minutes starts a new session) and calculate each session's duration.
Return user_id, session_num, and session_duration_minutes (whole minutes, rounded down). Use LAG() to find gaps between events.
Skills: advanced window functions with session detection
Tables
user_events 13 rows
- event_id integer PK
- user_id integer
- event_time text
- event_type text
| event_id | user_id | event_time | event_type |
|---|---|---|---|
| 1 | 1 | 2025-01-05 10:00:00 | page_view |
| 2 | 1 | 2025-01-05 10:05:00 | click |
| 3 | 1 | 2025-01-05 10:15:00 | page_view |