Quick Overview

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.

Transform clickstream with pandas sessionization

Company: OneMain Financial

Role: Data Scientist

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

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).

Overview: 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.

Read the full OneMain Financial Data Scientist interview experience this question came from

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: 1) Define each event's effective timestamp as COALESCE(event_ts, server_log_ts) (i.e., impute missing event_ts using server_log_ts). 2) 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. 3) 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.) 4) For each session, define session duration as (max_effective_ts - min_effective_ts) in seconds. A session with one event has duration 0. 5) 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).

Tables

events(event_id BIGINT, user_id INT, event_ts TIMESTAMP, url VARCHAR(200), server_log_ts TIMESTAMP)

Hints

  1. Impute the timestamp with COALESCE(event_ts, server_log_ts) before ordering and sessionizing.
  2. Use LAG to compute gaps and a running SUM over a new-session flag to assign session numbers.

Loading coding console...