PySpark data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 24 minutes. Part of the Pro drill bank.
Assign session ids with a 30-minute inactivity gap and summarise each session. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.
clicks has user_id and ts (a timestamp string); rows are not in order. A user's events belong to the same session until there is a gap longer than 30 minutes (more than 1800 seconds) between consecutive events; the next event starts a new session. A gap of exactly 30 minutes does not start a new session. Return one row per session with: user_id session_id: the user id, a dash and the session number starting at 1 (u1-1, u1-2, ...) session_start, session_end: first and last ts in the session events: number of events Assign the DataFrame to result.
Input: clicks user_id | ts u1 | 2024-03-01 11:00:00 u1 | 2024-03-01 09:00:00 u1 | 2024-03-01 09:40:00 u1 | 2024-03-01 09:10:00 u2 | 2024-03-01 10:29:59 u2 | 2024-03-01 10:00:00 u2 | 2024-03-01 11:00:00 Output: user_id | session_id | session_start | session_end | events u1 | u1-1 | 2024-03-01 09:00:00 | 2024-03-01 09:40:00 | 3 u1 | u1-2 | 2024-03-01 11:00:00 | 2024-03-01 11:00:00 | 1 u2 | u2-1 | 2024-03-01 10:00:00 | 2024-03-01 10:29:59 | 2 u2 | u2-2 | 2024-03-01 11:00:00 | 2024-03-01 11:00:00 | 1 u1's events at 09:00, 09:10 and 09:40 form one session (the last gap is exactly 30 minutes); 11:00 starts a second. u2 splits after 10:29:59 because 11:00:00 is more than 30 minutes later.
Topics: lakebench, pyspark, sessionization, lag, cumulative sum.
More PySpark interview questions · All interview problems · Learn data engineering
Interview-style drill: Assign session ids with a 30-minute inactivity gap and summarise each session.
`clicks` has `user_id` and `ts` (a timestamp string); rows are not in order. A user's events belong to the same session until there is a gap **longer than 30 minutes** (more than 1800 seconds) between consecutive events; the next event starts a new session. A gap of exactly 30 minutes does not start a new session. Return one row per session with: - `user_id` - `session_id`: the user id, a dash and the session number starting at 1 (`u1-1`, `u1-2`, ...) - `session_start`, `session_end`: first and last `ts` in the session - `events`: number of events Assign the DataFrame to `result`.