SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.
From user_events, build sessions per user: a new session starts when the gap from the previous event is more than 30 minutes (or the event is the user's first). Return one row per session: user_id session_id: integer starting at 1 per user in time order session_start: MIN(event_time) session_end: MAX(event_time) Order by user_id, session_id.
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
Interview-style drill: Build sessions where a gap > 30 minutes starts a new session.
From `user_events`, build sessions per user: a new session starts when the gap from the previous event is more than 30 minutes (or the event is the user's first). Return one row per session: - `user_id` - `session_id`: integer starting at 1 per user in time order - `session_start`: MIN(event_time) - `session_end`: MAX(event_time) Order by `user_id`, `session_id`.