Coding questions · OneMain Financial
Transform clickstream with pandas sessionization
This question evaluates proficiency in time-series data manipulation, sessionization logic, timestamp imputation, robust ordering of out-of-order events, and scalable chunked processing using pandas or SQL-based techniques.
Given a pandas DataFrame events with columns [user_id:int, ts:str ISO8601 or NaT, url:str, server_log_ts:datetime], build 30-minute inactivity sessions per user: 1) Use server_log_ts to impute ts when ts is missing; 2) Robustly sort events per user with potentially out-of-order rows; 3) Define session_id when the gap > 30 minutes; 4) Compute, for each user, session_count, median_session_duration, and the 95th percentile of pages per session; 5) Ensure the solution works in streaming-sized chunks (cannot load all users into memory). Provide vectorized code sketches and explain correctness on edge cases (exactly-30-minute gaps, duplicated events, DST shifts).
Clickstream Sessionization (30-minute inactivity) with Imputed Timestamps
You are given clickstream events that may have a missing client timestamp.
Build 30-minute inactivity sessions per user and then compute session-level rollups per user.
Requirements:
- Define each event's effective timestamp as COALESCE(event_ts, server_log_ts) (i.e., impute missing event_ts using server_log_ts).
- Robustly order events per user by effective timestamp (assume raw rows may be out-of-order). If two events have the same effective timestamp for a user, break ties by event_id.
- A new session starts when the time gap from the previous event for that user is strictly greater than 30 minutes. (Exactly 30 minutes is NOT a new session.)
- For each session, define session duration as (max_effective_ts - min_effective_ts) in seconds. A session with one event has duration 0.
- Return, for each user:
- session_count
- median_session_duration_seconds
- p95_pages_per_session (95th percentile of page counts per session)
Assume timestamps are stored in UTC (so DST shifts do not affect ordering or gaps).
Table events
| column | type | constraints |
|---|---|---|
| event_id | BIGINT | PRIMARY KEY, NOT NULL |
| user_id | INT | NOT NULL |
| event_ts | TIMESTAMP | |
| url | VARCHAR(200) | NOT NULL |
| server_log_ts | TIMESTAMP | NOT NULL |
Sample data:
| event_id | event_ts | server_log_ts | url | user_id |
|---|---|---|---|---|
| 1 | 2025-05-01 10:00:00 | 2025-05-01 10:00:05 | /home | 1 |
| 2 | NULL | 2025-05-01 10:10:00 | /prod | 1 |
| 3 | 2025-05-01 10:40:00 | 2025-05-01 10:40:02 | /cart | 1 |
| 4 | 2025-05-01 11:20:00 | 2025-05-01 11:20:00 | /checkout | 1 |
| 5 | 2025-05-01 11:21:00 | 2025-05-01 11:21:00 | /confirm | 1 |
| 6 | 2025-05-01 10:05:00 | 2025-05-01 10:05:01 | /search | 1 |
| 7 | 2025-05-02 09:00:00 | 2025-05-02 09:00:00 | /home | 2 |
| 8 | 2025-05-02 09:45:00 | 2025-05-02 09:45:00 | /about | 2 |
| 9 | NULL | 2025-05-02 10:00:00 | /contact | 2 |
| 10 | 2025-05-02 11:00:00 | 2025-05-02 11:00:00 | /pricing | 2 |
| 11 | 2025-05-02 09:00:00 | 2025-05-02 09:00:10 | /home | 2 |
Expected output
One row per user with the number of sessions, the median session duration (seconds), and the 95th percentile of pages per session computed from per-session page counts.
| median_session_duration_seconds | p95_pages_per_session | session_count | user_id |
|---|---|---|---|
| 1230 | 3.9 | 2 | 1 |
| 0 | 2 | 3 | 2 |
Hints
- Impute the timestamp with COALESCE(event_ts, server_log_ts) before ordering and sessionizing.
- Use LAG to compute gaps and a running SUM over a new-session flag to assign session numbers.
- Aggregate to per-session rows first, then compute per-user percentiles from those per-session metrics.