Coding questions · Apple
Detect sessions and gaps using SQL LEAD
This question evaluates proficiency in SQL time-series and event-stream manipulation, focusing on window-function techniques for sessionization, timestamp arithmetic, and aggregation to produce session-level metrics, targeting Data Manipulation (SQL/Python) competencies relevant to data scientist roles.
Write a single ANSI-SQL query that (a) assigns per-user session_ids when the gap between consecutive events exceeds 30 minutes, (b) computes session_start, session_end, event_count, session_length_seconds, and next_session_gap_seconds (time from this session_end to the next session_start for the same user), and (c) uses window functions including LEAD at least once. Schema:
- events(user_id INT, ts TIMESTAMP, action VARCHAR) Sample data:
+---------+---------------------+---------+
| user_id | ts | action |
+---------+---------------------+---------+
| 1 | 2025-08-01 09:00:00 | view |
| 1 | 2025-08-01 09:10:00 | click |
| 1 | 2025-08-01 10:00:00 | view |
| 2 | 2025-08-01 12:00:00 | view |
| 2 | 2025-08-01 12:20:00 | click |
| 2 | 2025-08-01 12:45:00 | purchase|
| 2 | 2025-08-01 14:00:00 | view |
+---------+---------------------+---------+
Requirements:
- Use LAG to detect new sessions and a running SUM to form session_ids per user.
- Use LEAD(ts) to compute next_event_ts and derive next_session_gap_seconds.
- Return columns: user_id, session_id, session_start, session_end, event_count, session_length_seconds, next_session_gap_seconds (NULL if no next session).
Detect Sessions and Gaps with SQL Window Functions
Using PostgreSQL, assign each user's events to sessions. A new session begins only when the gap from the preceding event is greater than 30 minutes; a gap of exactly 30 minutes stays in the same session. Return user_id, a 1-based session_id, session_start, session_end, event_count, session_length_seconds, and next_session_gap_seconds (the seconds from this session's final event to the next session's first event, or NULL for the user's last session). Use LAG to identify session boundaries, a running SUM to form session IDs, and LEAD(ts) to identify the next event. Order by user_id and session_id.
Table events
| column | type | constraints |
|---|---|---|
| user_id | INTEGER | NOT NULL |
| ts | TIMESTAMP | NOT NULL |
| action | TEXT | NOT NULL |
Sample data:
| action | ts | user_id |
|---|---|---|
| view | 2025-08-01 09:00:00 | 1 |
| click | 2025-08-01 09:10:00 | 1 |
| view | 2025-08-01 10:00:00 | 1 |
| view | 2025-08-01 12:00:00 | 2 |
| click | 2025-08-01 12:20:00 | 2 |
| purchase | 2025-08-01 12:45:00 | 2 |
| view | 2025-08-01 14:00:00 | 2 |
| view | 2025-08-02 15:00:00 | 3 |
| click | 2025-08-02 15:30:00 | 3 |
| purchase | 2025-08-02 16:00:01 | 3 |
| view | 2025-08-03 08:00:00 | 4 |
Expected output
| user_id | session_id | session_start | session_end | event_count | session_length_seconds | next_session_gap_seconds |
|---|---|---|---|---|---|---|
| 1 | 1 | 2025-08-01 09:00:00 | 2025-08-01 09:10:00 | 2 | 600 | 3000 |
| 1 | 2 | 2025-08-01 10:00:00 | 2025-08-01 10:00:00 | 1 | 0 | NULL |
| 2 | 1 | 2025-08-01 12:00:00 | 2025-08-01 12:45:00 | 3 | 2700 | 4500 |
| 2 | 2 | 2025-08-01 14:00:00 | 2025-08-01 14:00:00 | 1 | 0 | NULL |
| 3 | 1 | 2025-08-02 15:00:00 | 2025-08-02 15:30:00 | 2 | 1800 | 1801 |
| 3 | 2 | 2025-08-02 16:00:01 | 2025-08-02 16:00:01 | 1 | 0 | NULL |
| 4 | 1 | 2025-08-03 08:00:00 | 2025-08-03 08:00:00 | 1 | 0 | NULL |
Hints
- Compute previous and next timestamps at event grain before assigning sessions.
- A strict gap greater than 30 minutes starts a new session; exactly 30 minutes does not.
- After the running session sum, aggregate at user/session grain and retain the next timestamp only across a session boundary.