Transform messy transactions with pandas

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

Monthly user spend features with time zones, deduplication, and rolling medians

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.

Table transactions

column	type	constraints
txn_id	INT	NOT NULL
user_id	VARCHAR(10)	NOT NULL
ts_iso	TIMESTAMP WITH TIME ZONE	NOT NULL
amount	DECIMAL(10,2)	NOT NULL
merchant_cat	VARCHAR(50)	NOT NULL
type	VARCHAR(20)	NOT NULL
updated_at	TIMESTAMP WITH TIME ZONE	NOT NULL
dup_hint	VARCHAR(20)

Sample data:

amount	dup_hint	merchant_cat	ts_iso	txn_id	type	updated_at	user_id
120	A	groceries	2024-03-31T23:55:00-0700	1	purchase	2024-04-01T00:02:00Z	U1
-20	B	groceries	2024-03-31T23:58:10-0700	2	refund	2024-04-01T00:05:00Z	U1
15	C	digital	2024-04-01T07:01:00+0200	3	purchase	2024-04-01T05:05:00Z	U2
15	C	digital	2024-04-01T07:01:00+0200	3	purchase	2024-04-01T05:06:10Z	U2
500	D	travel	2024-04-02T10:00:00Z	4	purchase	2024-04-02T10:01:00Z	U3
500	E	travel	2024-06-15T23:59:59-0400	5	chargeback	2024-06-20T12:00:00Z	U3
0	F	fees	2024-06-01T00:00:10Z	6	purchase	2024-06-01T00:00:20Z	U1
200	G	electronics	2024-06-30T23:59:59-0700	7	purchase	2024-07-01T08:00:00Z	U2

Table users

column	type	constraints
user_id	VARCHAR(10)	PRIMARY KEY
signup_ts_iso	TIMESTAMP WITH TIME ZONE	NOT NULL
country	VARCHAR(2)	NOT NULL
tz_name	VARCHAR(64)	NOT NULL

Sample data:

country	signup_ts_iso	tz_name	user_id
US	2024-01-15T12:00:00Z	America/Los_Angeles	U1
US	2024-02-20T09:30:00Z	America/New_York	U2
GB	2023-12-01T00:00:00Z	Europe/London	U3

Expected output

One row per user as of 2024-07-31: 5-month activity/spend/refund rollup plus the July rolling 3-month median and top-3 July merchant categories, ordered by user_id.

cat1_2024_07	cat2_2024_07	cat3_2024_07	had_any_refund_last_5m	months_active_last_5m	rolling_3m_median_spend_at_2024_07	total_net_spend_last_5m	user_id
None	None	None	true	2	0	120.00	U1
electronics	None	None	false	2	200.00	215.00	U2
None	None	None	true	1	NULL	500.00	U3

Hints

1. 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.
2. 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.
3. Cross join users with a generate_series of the five month starts, then LEFT JOIN the per-month aggregates so inactive months still appear and default to 0 / false.