Transform messy transactions with pandas
Company: Boston Consulting Group
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
You are given two CSVs.
transactions.csv
- Columns: txn_id, user_id, ts_iso (ISO8601 with timezone), amount (decimal USD; refunds negative), merchant_cat, type {purchase, refund, chargeback}, updated_at (last write wins), dup_hint (string that can be identical across near-duplicate rows)
- Sample rows:
1, U1, 2024-03-31T23:55:00-0700, 120.00, groceries, purchase, 2024-04-01T00:02:00Z, A
2, U1, 2024-03-31T23:58:10-0700, -20.00, groceries, refund, 2024-04-01T00:05:00Z, B
3, U2, 2024-04-01T07:01:00+0200, 15.00, digital, purchase, 2024-04-01T05:05:00Z, C
3, U2, 2024-04-01T07:01:00+0200, 15.00, digital, purchase, 2024-04-01T05:06:10Z, C (duplicate with later updated_at)
4, U3, 2024-04-02T10:00:00Z, 500.00, travel, purchase, 2024-04-02T10:01:00Z, D
5, U3, 2024-06-15T23:59:59-0400, 500.00, travel, chargeback, 2024-06-20T12:00:00Z, E
6, U1, 2024-06-01T00:00:10Z, 0.00, fees, purchase, 2024-06-01T00:00:20Z, F
7, U2, 2024-06-30T23:59:59-0700, 200.00, electronics, purchase, 2024-07-01T08:00:00Z, G
users.csv
- Columns: user_id, signup_ts_iso, country, tz_name
- Sample rows:
U1, 2024-01-15T12:00:00Z, US, America/Los_Angeles
U2, 2024-02-20T09:30:00Z, US, America/New_York
U3, 2023-12-01T00:00:00Z, GB, Europe/London
Task (write idiomatic, production-ready pandas code without groupby.apply or explicit Python loops over rows; assume data can be 100M+ rows):
1) Read both files, parse timestamps, and normalize ts_iso to UTC. Deduplicate transactions by txn_id keeping only the row with the max updated_at. Sanity-check and drop rows where amount is NaN, type is invalid, or ts_iso is outside [2023-01-01, 2025-12-31].
2) Exclude refunds and chargebacks from spend features but keep them in a separate flag. Define net_spend as sum of amounts over type=='purchase' only.
3) Build month-level features per user for the window 2024-03-01 through 2024-07-31 (inclusive, calendar months in the user’s tz_name, but aggregated after converting to UTC to avoid DST duplication):
- active_month (1 if user has ≥1 purchase in that local month, else 0),
- monthly_net_spend (sum of positive purchase amounts in that local month),
- rolling_3m_median_spend computed over active months only (skip months with active_month=0; do not fill implicit zeros), aligned to month end.
4) For each user-month, compute the top-3 merchant_cat by monthly_net_spend and emit them as categorical features cat1, cat2, cat3. Break ties by larger monthly_net_spend then lexicographic merchant_cat. If <3 categories exist, fill with 'None'.
5) Output one row per user for the snapshot date 2024-07-31 containing: user_id, months_active_last_5m, total_net_spend_last_5m, had_any_refund_last_5m (boolean from refunds/chargebacks), rolling_3m_median_spend_at_2024_07, cat1_2024_07, cat2_2024_07, cat3_2024_07. Ensure results are idempotent if you re-run on the same inputs, and memory-efficient (hint: use categorical dtypes, proper indexing, and avoid exploding intermediate DataFrames). Explain any edge cases you handle (DST boundaries, zero-amount rows, duplicate near-same rows via dup_hint).
Overview: This question evaluates proficiency in large-scale data manipulation and feature engineering using pandas, covering timestamp normalization across time zones, deduplication and last-write-wins semantics, data validation, memory-efficient aggregation, rolling-window statistics, and top-k categorical extraction for merchant categories.
## Monthly user-spend features with time zones, deduplication, and a rolling median
You are given two tables, `transactions` and `users`. Write a **single PostgreSQL query** that produces **exactly one row per user** as a snapshot at **2024-07-31**, with a fixed set of feature columns.
All timestamp columns are `TIMESTAMP WITH TIME ZONE`. The analysis revolves around **each user's local calendar months**, derived from their `tz_name` (an IANA zone such as `America/Los_Angeles`).
### Business rules
**1) Clean and deduplicate the transactions**
- Start from `transactions`.
- Keep only rows where **all** of the following hold:
- `amount IS NOT NULL`
- `type` is one of `('purchase', 'refund', 'chargeback')`
- `ts_iso` is within the inclusive UTC range `[2023-01-01 00:00:00+00, 2025-12-31 23:59:59+00]`
- **Deduplicate by `txn_id`**: when multiple rows share a `txn_id`, keep only the one with the **greatest `updated_at`** ("last write wins") and drop the rest.
**2) Local month of each transaction**
- Convert `ts_iso` to the user's local time with `AT TIME ZONE u.tz_name`, then truncate to the first day of the local month (e.g. local `2024-04-15` → `2024-04-01`). DST and day/month boundaries must be respected.
**3) Analysis window**
- For every user, consider the **five local months 2024-03, 2024-04, 2024-05, 2024-06, 2024-07** (i.e. month starts `2024-03-01` … `2024-07-01`). A user-month with no qualifying transactions must still be considered (it is simply inactive).
**4) Per-user, per-month building blocks**
- `active_month` = 1 if the user has **at least one `purchase`** (any amount) in that local month, else 0.
- `monthly_net_spend` = sum of **positive purchase amounts** (`type = 'purchase' AND amount > 0`) in that local month. Zero/negative purchase amounts do **not** add to spend (so a month with only a $0 purchase is active with `monthly_net_spend = 0`). Refunds and chargebacks never count toward spend.
- A month "has a refund" if it contains at least one `refund` **or** `chargeback`.
**5) Rolling 3-month median (the key step)**
- For each user and month `M`, take the window of `M` and the two preceding calendar months (e.g. for `2024-07` the window is `2024-05, 2024-06, 2024-07`).
- Within that window, keep only months where `active_month = 1`, and compute the **discrete median** (`percentile_disc(0.5)`) of their `monthly_net_spend`.
- If the window has no active months, the rolling median is `NULL`.
**6) Top merchant categories for July 2024**
- For local month `2024-07` only, for each `merchant_cat` sum the positive purchase amounts (`type = 'purchase' AND amount > 0`) to get `category_net_spend`.
- Rank categories by `category_net_spend` **descending**, breaking ties by `merchant_cat` **ascending (lexicographic)**.
- `cat1_2024_07`, `cat2_2024_07`, `cat3_2024_07` are the 1st, 2nd and 3rd ranked categories. Fill any missing position with the literal string `'None'` (so a user with no positive July spend gets `'None'` in all three).
### Required output (one row per user, ordered by `user_id` ascending)
| column | meaning |
|---|---|
| `user_id` | the user |
| `months_active_last_5m` | count of months in 2024-03…2024-07 with `active_month = 1` |
| `total_net_spend_last_5m` | sum of `monthly_net_spend` over 2024-03…2024-07 |
| `had_any_refund_last_5m` | `TRUE` if any of those months has a refund or chargeback, else `FALSE` |
| `rolling_3m_median_spend_at_2024_07` | the rolling 3-month median for month `2024-07` (NULL if May–Jul has no active months) |
| `cat1_2024_07`, `cat2_2024_07`, `cat3_2024_07` | top-3 July merchant categories, `'None'` where missing |
Every user in `users` must appear, even with no qualifying transactions. Use only relational constructs (joins, CTEs, window functions, ordered-set aggregates) — no procedural code. Sort the final result by `user_id` ascending.
Tables
transactions(txn_id INT, user_id VARCHAR(10), ts_iso TIMESTAMP WITH TIME ZONE, amount DECIMAL(10,2), merchant_cat VARCHAR(50), type VARCHAR(20), updated_at TIMESTAMP WITH TIME ZONE, dup_hint VARCHAR(20))
users(user_id VARCHAR(10), signup_ts_iso TIMESTAMP WITH TIME ZONE, country VARCHAR(2), tz_name VARCHAR(64))
Hints
- Ordered-set aggregates like percentile_disc cannot be used as window functions in PostgreSQL. Build the rolling 3-month window with a self-join (current month joined to the prior 3 months) and aggregate with GROUP BY instead.
- Derive each transaction's local month with date_trunc('month', ts_iso AT TIME ZONE u.tz_name)::date so DST and month-boundary cases land in the correct local month.