Analyze time-zoned events with pandas
Company: Voleon
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two pandas DataFrames. events columns: user_id:int, ts:str ISO-8601 with timezone (e.g., '2025-08-31T23:58:43-07:00'), event:str in {'signup','login','purchase'}, revenue:str that may include currency symbols (e.g., '$12.34', '€9,50') or be null, device_id:str, session_id:str (may be duplicated), source:str (may be null). users columns: user_id:int, signup_ts:str UTC, tz:str IANA timezone (e.g., 'America/Los_Angeles'), is_bot:bool, country:str, plan:str in {'free','pro','enterprise'}. Tasks: (1) Clean and normalize: make all timestamps timezone-aware; deduplicate events defined as same (user_id, event, session_id) within a 5-minute window keeping the earliest ts; drop events for users missing in users; explain how you would do this without chained assignment. (2) Define an "active day" as any local-day (in each user's tz) with at least one non-'signup' event. Compute DAU per local date for 2025-08-25 through 2025-09-01 inclusive, excluding users with is_bot=True and excluding device_ids that appear on ≥20 distinct user_id values (shared devices). Return a DataFrame with local_date, dau. (3) Compute 7-day retention for cohorts with signup_date (UTC) between 2025-08-24 and 2025-08-31. A user is "retained" if they have any non-'signup' event on the 7th day after signup in their local tz. Return columns: signup_date, cohort_size, retained, retention_rate, and a Wilson 95% CI for the rate. (4) Parse revenue strings and convert to USD given an FX mapping fx = {'USD':1.0,'EUR':1.08,'JPY':0.0068,...} inferred from symbols; impute missing revenue for 'purchase' rows using the median revenue over the last 30 days ending 2025-09-01 within (country, plan) groups; then compute ARPU for the last 7 days ending 2025-09-01 for non-bot users. (5) Provide vectorized pandas code sketches (no Python loops), discuss expected memory footprint and computational complexity for 100 million events, and outline a chunked processing strategy to keep peak RAM under 8 GB (e.g., category dtypes, read_csv dtype map, sorted merges, and groupby with observed=True).
Overview: This question evaluates proficiency in data manipulation and analysis with pandas and SQL, covering timezone-aware timestamp handling, event deduplication, cohort and retention metrics, currency parsing and FX conversion, revenue imputation, ARPU calculation, and scalability for high-volume event data.
Clean events: parse time-zoned timestamps, drop unknown users, and deduplicate within 5 minutes
You are given two tables:
- `events` has an ISO-8601 timestamp string with a timezone offset (e.g., `2025-08-31T23:58:43-07:00`).
- `users` contains the valid user population.
Perform the following cleaning steps:
1) Parse `events.ts` into a timezone-aware timestamp.
2) Drop events whose `user_id` does not exist in `users`.
3) Deduplicate events defined as the same `(user_id, event, session_id)` occurring within a 5-minute window, keeping the earliest event in each such window.
Return exactly one row with:
- `raw_event_count`
- `dropped_missing_users`
- `dropped_duplicates`
- `cleaned_event_count`
Assume PostgreSQL.
Tables
users(user_id INT, signup_ts TIMESTAMPTZ, tz VARCHAR(64), is_bot BOOLEAN, country VARCHAR(2), plan VARCHAR(16))
events(event_id INT, user_id INT, ts VARCHAR(40), event VARCHAR(16), revenue VARCHAR(32), device_id VARCHAR(32), session_id VARCHAR(32), source VARCHAR(32))
Hints
- In PostgreSQL, `(iso8601_string)::timestamptz` parses timezone offsets correctly.
- Use window functions (LAG + cumulative SUM) to form 5-minute clusters, then keep the first row per cluster.
DAU by user local date (exclude bots and shared devices)
You have two PostgreSQL tables that track product activity.
**`users`** — one row per registered user:
| column | type | notes |
|---|---|---|
| `user_id` | INT | primary key |
| `signup_ts` | TIMESTAMPTZ | when the user signed up |
| `tz` | VARCHAR(64) | the user's IANA time zone (e.g. `'America/Los_Angeles'`, `'Asia/Tokyo'`, `'UTC'`) |
| `is_bot` | BOOLEAN | `TRUE` for known bot accounts |
| `country` | VARCHAR(2) | ISO country code |
| `plan` | VARCHAR(16) | subscription plan |
**`events`** — one row per logged event:
| column | type | notes |
|---|---|---|
| `event_id` | INT | primary key |
| `user_id` | INT | references `users.user_id` |
| `ts` | VARCHAR(40) | event timestamp as an **ISO-8601 string with an explicit offset** (e.g. `'2025-08-31T23:58:43-07:00'`, `'2025-08-27T15:00:00Z'`). Cast it to `timestamptz` before using it. |
| `event` | VARCHAR(16) | event type, e.g. `'signup'`, `'login'`, `'purchase'` |
| `revenue` | VARCHAR(32) | free-form revenue string (ignore for this question) |
| `device_id` | VARCHAR(32) | the device the event came from |
| `session_id` | VARCHAR(32) | session identifier |
| `source` | VARCHAR(32) | acquisition source (nullable) |
**Definitions**
- A user's **local date** for an event is the calendar date of that event in *that user's* time zone: `((ts)::timestamptz AT TIME ZONE users.tz)::date`.
- A user has an **active day** on a given local date if they have at least one event on that local date whose `event` is **not** `'signup'`.
- **DAU** for a date = the number of **distinct** users who have an active day on that date.
**Task**
Compute DAU per local date for the inclusive range **2025-08-25 through 2025-09-01** (8 dates), applying these exclusions:
1. Exclude all events of users where `is_bot = TRUE`.
2. Exclude any event whose `device_id` is a **shared device** — defined as a `device_id` that appears across **20 or more distinct `user_id` values** (counted over the whole `events` table).
3. Exclude `'signup'` events (they never count toward an active day).
**Output:** Return exactly one row for **every** date in the range, even dates with no qualifying activity (DAU = 0). Two columns:
- `local_date` — the date as text in `'YYYY-MM-DD'` format
- `dau` — the distinct active-user count for that date
Order the result by `local_date` ascending.
Tables
users(user_id INT, signup_ts TIMESTAMPTZ, tz VARCHAR(64), is_bot BOOLEAN, country VARCHAR(2), plan VARCHAR(16))
events(event_id INT, user_id INT, ts VARCHAR(40), event VARCHAR(16), revenue VARCHAR(32), device_id VARCHAR(32), session_id VARCHAR(32), source VARCHAR(32))
Hints
- Convert each event to its owner's local date with `((ts)::timestamptz AT TIME ZONE users.tz)::date` before comparing to the date window.
- Find shared devices with a separate aggregate over the full events table: `GROUP BY device_id HAVING COUNT(DISTINCT user_id) >= 20`, then exclude them.
7-day retention by signup_date (UTC) with Wilson 95% confidence interval
Compute 7-day retention for cohorts with `signup_date` (UTC) between 2025-08-24 and 2025-08-31 inclusive.
Definitions:
- Cohort key: `signup_date_utc = (users.signup_ts AT TIME ZONE 'UTC')::date`.
- A user is retained if they have any non-`'signup'` event whose **local date** (in `users.tz`) equals the user's signup local date + 7 days.
- `signup_local_date = (users.signup_ts AT TIME ZONE users.tz)::date`
- `event_local_date = (event_ts AT TIME ZONE users.tz)::date`
Return columns:
- `signup_date`
- `cohort_size`
- `retained`
- `retention_rate`
- `wilson_lower_95`
- `wilson_upper_95`
Use Wilson score interval with z = 1.96.
Assume PostgreSQL.
Tables
users(user_id INT, signup_ts TIMESTAMPTZ, tz VARCHAR(64), is_bot BOOLEAN, country VARCHAR(2), plan VARCHAR(16))
events(event_id INT, user_id INT, ts VARCHAR(40), event VARCHAR(16), revenue VARCHAR(32), device_id VARCHAR(32), session_id VARCHAR(32), source VARCHAR(32))
Hints
- Cohort is based on UTC date, but the retention check is based on the user’s local date.
- Wilson interval can be computed directly in SQL using numeric arithmetic.
Parse multi-currency revenue, impute missing purchase revenue, and compute ARPU (USD)
You are given purchase events where `events.revenue` may look like:
- `'$12.34'` (USD)
- `'€9,50'` (EUR with comma decimal)
- `'¥1500'` (JPY)
- NULL
You are also given an FX table `fx_rates(currency_code, usd_rate)` that converts native currency amounts into USD.
Tasks:
1) Parse `events.revenue` into a numeric native amount and infer `currency_code` from the symbol (`$`=USD, `€`=EUR, `¥`=JPY). Convert to USD using `fx_rates`.
2) For rows where `event='purchase'` and revenue is NULL, impute `revenue_usd` using the median `revenue_usd` over the date range 2025-08-03 through 2025-09-01 (inclusive) within `(users.country, users.plan)`.
3) Compute ARPU for the UTC-date range 2025-08-26 through 2025-09-01 (inclusive) for non-bot users:
- Numerator: total imputed purchase revenue in USD in the range.
- Denominator: distinct non-bot users who have at least one non-'signup' event in the range.
Return one row with `active_users`, `total_revenue_usd`, `arpu_usd`.
Assume PostgreSQL.
Tables
users(user_id INT, signup_ts TIMESTAMPTZ, tz VARCHAR(64), is_bot BOOLEAN, country VARCHAR(2), plan VARCHAR(16))
events(event_id INT, user_id INT, ts VARCHAR(40), event VARCHAR(16), revenue VARCHAR(32), device_id VARCHAR(32), session_id VARCHAR(32), source VARCHAR(32))
fx_rates(currency_code CHAR(3), usd_rate DECIMAL(10,4))
Hints
- Use `REGEXP_REPLACE` to strip currency symbols and separators; for EUR, replace comma with dot before casting.
- Median in PostgreSQL can be computed with `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...)`.