Sessionize clickstream

PySpark data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 24 minutes. Part of the Pro drill bank.

To understand how users behave, you group their events into sessions. A new session starts when the user has been quiet for too long. Here, 'too long' means more than 30 minutes.

The table clicks has user_id and ts (a timestamp as text). The rows are not in order. For each user, sort the events by time. The events belong to the same session until there is a gap of more than 30 minutes (more than 1800 seconds) between two consecutive events. A gap of exactly 30 minutes does not start a new session. Return 1 row for each session with user_id, session_id (the user id, a dash, and the session number starting at 1, like u1-1), session_start and session_end (the first and last ts in the session), and events (the number of events). The editor creates the table clicks and ends with result = clicks. Change the last line to build your answer.

Requirements

• Use lag to find the previous event time for the same user.
• Mark a new session when there is no previous event or the gap is greater than 1800 seconds.
• Number the sessions with a running sum of the marks.
• Return user_id, session_id, session_start, session_end, events, sorted by user and start, in result.

Constraints

• A gap of exactly 1800 seconds keeps the same session. Use > 1800.
• The session number starts at 1 for every user.
• The window must be ordered by ts and partitioned by user_id.

Examples

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