events logs what users did: user_id, timestamp (text) and action (page_load, scroll or page_exit). A user has at most one session a day. It runs from their LAST page_load of that day to the FIRST page_exit after it. A day with no page_exit after its last page_load has no session. Find each user's average session length in seconds. Return result with the columns user_id and avg_seconds, one row for every user who had at least one session.
The data
events24 rows
| user_idint64 | timestampobject | actionobject |
|---|
| 0 | 2025-04-01 09:00:00 | page_load |
| 0 | 2025-04-01 09:03:20 | scroll |
| 0 | 2025-04-01 09:10:00 | page_exit |
| 0 | 2025-04-02 13:00:00 | page_load |
| 0 | 2025-04-02 13:05:00 | page_load |
| 0 | 2025-04-02 13:25:00 | page_exit |
and 18 more rows
Target output
| user_id | avg_seconds |
|---|
| 0 | 900 |
| 1 | 1800 |
| 2 | 900 |
| 4 | 620 |
| 5 | 900 |
What this question tests
Tests turning an event log into sessions: pairing a start event with the right end event, per user per day. The common shortcut, latest load and earliest exit of the day, gives negative sessions on a day that has two of them, and interviewers know it.
More Python interview questions