28. Sessionize a Clickstream

Difficulty: Hard · Topics: Window Functions, Dates, Null Handling, Conditional Logic, Filtering & Selection

A session is a sequence of clicks by one user where no two consecutive clicks are more than 30 minutes apart. A gap of exactly 30 minutes stays in the same session.

Number each user's sessions 1, 2, 3, … in time order. Return user_id, ts, session.

Row order does not matter; column names must match. Your code is graded on 3 test cases, including hidden edge cases.

Sample data

clicks

user_id	ts
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 11:20:00
u2	2024-01-01 09:00:00
u2	2024-01-01 12:00:00

Expected output

user_id	ts	session
u1	2024-01-01 10:00:00	1
u1	2024-01-01 10:10:00	1
u1	2024-01-01 11:00:00	2
u1	2024-01-01 11:20:00	2
u2	2024-01-01 09:00:00	1
u2	2024-01-01 12:00:00	2

Hints

Hint 1

Compare each click with the previous one: F.lag("ts") over a window per user ordered by ts.

Hint 2

Seconds between two timestamps: F.unix_timestamp("ts") - F.unix_timestamp("prev_ts").

Hint 3

Flag a new session with 1 (first click, or gap > 1800 s) and take a running sum of the flags.

PySpark functions you'll practise

• Lag
• Window spec
• sum
• isNull
• when / otherwise
• unix_timestamp
• select
• orderBy

Related problems

• Build an SCD Type 2 History · Hard · Window Functions
• Trailing 3 Calendar Days (Range Frame) · Hard · Window Functions
• Three-Day Login Streak · Hard · Window Functions
• Reconcile Two Sources (Full Outer Join) · Hard · Joins
• Fill Missing Dates (Calendar Spine) · Hard · Joins

Browse

Topics: Window Functions · Joins · Aggregations · Pivot, Unpivot & Rollup · Arrays · Null Handling · Conditional Logic · Dates · Filtering & Selection · Strings

Difficulty: Easy · Medium · Hard · PySpark interview roadmap · All problems