Sessionize App Events
Split each user's events into sessions. A new session starts with the user's first event, and whenever more than 30 minutes have passed since that user's previous event. A gap of exactly 30 minutes stays in the same session. Events with the same timestamp are ordered by event_id.
For each user report the number of sessions and the average number of events per session, ROUND(x, 2).
Return columns user_id, sessions, avg_events_per_session. Row order does not matter.
Schema
CREATE TABLE app_events ( event_id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, event_time TEXT NOT NULL, event_name TEXT NOT NULL );
Sample data
app_events
| event_id | user_id | event_time | event_name |
|---|---|---|---|
| 1 | 1 | 2026-06-01 09:00:00 | open |
| 2 | 1 | 2026-06-01 09:10:00 | view |
| 3 | 1 | 2026-06-01 09:35:00 | view |
| 4 | 1 | 2026-06-01 10:30:00 | open |
| 5 | 2 | 2026-06-01 12:00:00 | open |
| 6 | 2 | 2026-06-01 12:05:00 | buy |
| 7 | 1 | 2026-06-01 10:31:00 | buy |
| 8 | 3 | 2026-06-01 08:00:00 | open |
Hint 1
Compare each event with the previous one for the same user using LAG.
Hint 2
Mark an event 1 if it starts a session, otherwise 0. The number of sessions is the sum of the marks.
Hint 3
Work in whole seconds with strftime('%s', ...). Fractional julianday differences can make exactly 30 minutes look slightly more.
Good practice for: Data Engineer, Data Analyst, Data Scientist, Machine Learning Engineer, Product Manager
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.