Voleon·Data Scientist·Technical Phone Screen
- Given two DataFrames (events and users), clean and normalize all timestamps to be timezone-aware, deduplicate events defined as the same (user_id, event, session_id) within a 5-minute window keeping the earliest timestamp, drop events for users not present in the users table, and explain how you'd avoid chained assignment throughout.
- Define an 'active day' as any local calendar day (in each user's own timezone) with at least one non-signup event. Compute daily active users per local date for a specific date range, excluding bots and excluding device IDs that appear across 20 or more distinct users (shared devices).
- Compute 7-day retention for signup cohorts over a specific week. A user counts as retained if they have any non-signup event on exactly the 7th day after signup, measured in their local timezone. Return cohort size, retained count, retention rate, and a Wilson 95% confidence interval.
- Parse revenue strings that may include currency symbols and locale-specific formatting (e.g. commas as decimal separators), convert to USD using a given FX rate mapping, impute missing revenue for purchase rows using median revenue over the prior 30 days within country-plan groups, then compute ARPU for the last 7 days for non-bot users.
- For a hypothetical dataset of 100 million events, provide vectorized pandas code sketches (no Python loops), discuss the expected memory footprint and computational complexity, and outline a chunked processing strategy to keep peak RAM under 8 GB using techniques like categorical dtypes, dtype maps on read, sorted merges, and groupby with observed=True.
“The chained assignment part is what got me.”