Sessionize events with a 30-minute gap

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 25 minutes. Part of the Pro drill bank.

Product analytics wants to know how long people stay and how much they do in one visit. The raw table has no visit id you can trust, so you have to build one: if a user has been quiet for more than 30 minutes, the next click starts a new visit. These visits are called sessions.

Use the raw ecommerce_events table (do not remove duplicates first). For each user_id, put the events in time order and split them into sessions. A new session starts when it is the user's first event, or when the gap since that user's previous event is more than 30 minutes. Return 1 row for each session with user_id, session_nbr, event_count, session_start and session_end. session_nbr starts at 1 for every user and goes up by 1 each time a new session starts. session_start is the time of the first event and session_end is the time of the last event. Order by user_id, then session_nbr.

Requirements

• Order each user's events by event_time and look at the gap to the previous event.
• Start a new session at the first event, and after any gap larger than 30 minutes.
• Number the sessions 1, 2, 3 for each user.
• Return event_count, session_start and session_end for every session.
• Use the raw table as it is. Do not collapse duplicate events.

Constraints

• A gap of exactly 30 minutes stays in the same session. Only a gap greater than 30 minutes starts a new one.
• session_nbr restarts at 1 for each user_id.
• Order the result by user_id, session_nbr.

Examples

Input: user_id | event_time U065 | 2026-08-02 07:16:47 U065 | 2026-08-02 20:46:39 U065 | 2026-08-02 21:01:25 Output: user_id | session_nbr | event_count | session_start | session_end U065 | 1 | 1 | 2026-08-02 07:16:47 | 2026-08-02 07:16:47 U065 | 2 | 2 | 2026-08-02 20:46:39 | 2026-08-02 21:01:25 Why this passes: The gap from 07:16:47 to 20:46:39 is over 13 hours (> 30 min), so it starts session 2. The next event at 21:01:25 is only ~15 minutes later, so it stays in session 2.

Topics: windows, LAG, sessions.

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