Sessionize events with 30-minute gaps

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Free to practice.

To understand how people use a product, analysts split a user's events into sessions. A session is a group of events that happen close together. A new session starts when more than 30 minutes have passed since the previous event, or when it is the user's very first event. From user_events, build the sessions for each user. Return 1 row for each session with user_id, session_id (1, 2, 3... counted per user in time order), session_start (the earliest event time) and session_end (the latest event time). Sort by user_id and session_id. The gap threshold is strictly more than 30 minutes.

Requirements

• Order each user's events by time.
• Start a new session when the gap to the previous event is more than 30 minutes, or when there is no previous event.
• Number the sessions per user, starting at 1.
• Return 1 row per session with its start and end time.
• Sort by user_id and session_id.

Constraints

• The table is user_events with the columns user_id and event_time.
• A gap of exactly 30 minutes does not start a new session. It must be greater than 30.
• Return only these 4 columns, in this order: user_id, session_id, session_start, session_end.

Examples

Input: user_events user_id | event_time U1 | 2024-01-01 10:00:00 U1 | 2024-01-01 10:10:00 U1 | 2024-01-01 11:00:00 U1 | 2024-01-01 12:00:00 U2 | 2024-01-01 09:00:00 U2 | 2024-01-01 09:20:00 Output: user_id | session_id | session_start | session_end U1 | 1 | 2024-01-01 10:00:00 | 2024-01-01 10:10:00 U1 | 2 | 2024-01-01 11:00:00 | 2024-01-01 11:00:00 U1 | 3 | 2024-01-01 12:00:00 | 2024-01-01 12:00:00 U2 | 1 | 2024-01-01 09:00:00 | 2024-01-01 09:20:00 Why this passes: Gaps of 50 and 60 minutes exceed 30 minutes and open new U1 sessions. U2's 20-minute gap stays in one session.

Topics: lakebench, sql, sessions, gaps.

More SQL interview questions · All interview problems · Learn data engineering