Coding questions · Gemini

Write SQL/Python for ACH fraud analytics

This question evaluates time-windowed analytics, SQL window functions and joins, ranking and event correlation, streaming de-duplication in Python, and domain knowledge of ACH payment and return behaviors within the Data Manipulation (SQL/Python) category.

As of today (2025-09-01), use the following schema and tiny samples to answer. Provide SQL for the SQL parts and Python for the Python part.

Schema

users(user_id INT, created_at TIMESTAMP, country TEXT)
transactions(txn_id TEXT, user_id INT, direction TEXT, method TEXT, amount_cents INT, created_at TIMESTAMP, status TEXT)
ach_returns(txn_id TEXT, return_code TEXT, returned_at TIMESTAMP)
devices(device_id TEXT, user_id INT, fingerprint TEXT, first_seen_at TIMESTAMP)
logins(login_id TEXT, user_id INT, device_id TEXT, ip TEXT, ts TIMESTAMP)

Sample rows

users
user_id | created_at | country
1 | 2025-06-10 08:00:00 | US
2 | 2025-08-01 12:00:00 | US
3 | 2025-08-20 09:00:00 | CA

transactions
txn_id | user_id | direction | method | amount_cents | created_at | status
t1 | 1 | credit | ACH | 50000 | 2025-08-31 10:00:00 | posted
t2 | 1 | credit | ACH | 40000 | 2025-08-31 18:00:00 | posted
t3 | 1 | credit | ACH | 30000 | 2025-09-01 09:30:00 | posted
t4 | 1 | debit | ACH | 20000 | 2025-09-02 12:00:00 | posted
t5 | 2 | credit | ACH | 150000 | 2025-08-30 02:00:00 | posted
t6 | 2 | credit | ACH | 150000 | 2025-09-01 02:30:00 | posted
t7 | 3 | credit | CARD | 70000 | 2025-09-01 11:00:00 | posted

ach_returns
txn_id | return_code | returned_at
t2 | R10 | 2025-09-03 08:00:00
t5 | R01 | 2025-09-05 09:00:00

devices
device_id | user_id | fingerprint | first_seen_at
d1 | 1 | abc123 | 2025-08-01 09:00:00
d2 | 1 | abc124 | 2025-08-31 17:30:00
d3 | 2 | zyx999 | 2025-08-29 22:00:00
d4 | 3 | abc123 | 2025-09-01 10:45:00

logins
login_id | user_id | device_id | ip | ts
l1 | 1 | d1 | 1.1.1.1 | 2025-08-31 09:50:00
l2 | 1 | d2 | 1.1.1.1 | 2025-08-31 17:40:00
l3 | 1 | d1 | 2.2.2.2 | 2025-09-01 09:25:00
l4 | 2 | d3 | 3.3.3.3 | 2025-09-01 02:25:00
l5 | 3 | d4 | 4.4.4.4 | 2025-09-01 10:50:00

Tasks A) SQL (window + joins): For each user, find the earliest rolling 24h window that ends on or before 2025-09-01 23:59:59 in which they have at least 3 ACH credit transactions and at least one of those credits is returned within 5 days of its created_at. Output: user_id, window_start, window_end, num_ach_credits_in_window, num_returns_within_5d, net_exposure_cents. Define net_exposure_cents as sum(amount_cents) of the ACH credits in that 24h window minus sum(amount_cents) of any ACH debits by the same user occurring between window_start and the earliest return timestamp for those credits. Use only transactions and ach_returns. B) SQL (ranking + window): As of 2025-09-01, for each user, rank their devices by the count of returned ACH credits linked to that device in the last 30 days (i.e., credits within 2025-08-02…2025-09-01 whose txn_id appears in ach_returns with returned_at in the same interval, where the device is the most recent login within 60 minutes before the credit). Output the top device per user with columns: user_id, device_id, fingerprint, returned_count_30d. C) Python: Given logins (streaming, possibly out of order by ≤5 minutes), collapse device fingerprints that differ by exactly one character (case-sensitive) into a canonical fingerprint (e.g., union-find over Hamming distance 1). Return all clusters where the canonical fingerprint is used by ≥3 distinct users within any 7-day window ending on 2025-09-01. Output list of tuples (canonical_fingerprint, window_start, window_end, distinct_user_count). State time/space complexity and how you handle late events and ties.

Earliest suspicious 24-hour ACH credit burst with returns and net exposure

You are given two tables: transactions and ach_returns.

Find, for each user, the earliest rolling 24-hour window (inclusive) that ends on or before 2025-09-01 23:59:59 where:

The user has at least 3 ACH credit transactions (status = posted) in that 24-hour window.
At least one of those credits is returned within 5 days of its created_at.

For each qualifying user, output:

user_id
window_start (timestamp)
window_end (timestamp)
num_ach_credits_in_window
num_returns_within_5d
net_exposure_cents

Definitions:

A rolling 24-hour window for a candidate window_end is [window_end - 24 hours, window_end].
num_returns_within_5d counts credits in the window whose ach_returns.returned_at is <= created_at + 5 days.
Let earliest_return_ts be the earliest returned_at among credits in the window that are returned within 5 days.
net_exposure_cents = (sum of amount_cents for the ACH credits in the 24-hour window) minus (sum of amount_cents for any ACH debits by the same user with created_at between window_start and earliest_return_ts, inclusive).

Use only transactions and ach_returns.

Render window_start and window_end as YYYY-MM-DD HH24:MI:SS in the output.

Table `transactions`

column	type	constraints
txn_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
direction	VARCHAR(10)	NOT NULL
method	VARCHAR(10)	NOT NULL
amount_cents	INT	NOT NULL
created_at	TIMESTAMP	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

amount_cents	created_at	direction	method	status	txn_id	user_id
50000	2025-08-31 10:00:00	credit	ACH	posted	t1	1
40000	2025-08-31 18:00:00	credit	ACH	posted	t2	1
30000	2025-09-01 09:30:00	credit	ACH	posted	t3	1
20000	2025-09-01 12:00:00	debit	ACH	posted	t4	1
150000	2025-08-30 02:00:00	credit	ACH	posted	t5	2
150000	2025-09-01 02:30:00	credit	ACH	posted	t6	2
70000	2025-09-01 11:00:00	credit	CARD	posted	t7	3

Table `ach_returns`

column	type	constraints
txn_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
return_code	VARCHAR(10)	NOT NULL
returned_at	TIMESTAMP	NOT NULL

Sample data:

return_code	returned_at	txn_id
R10	2025-09-01 20:00:00	t2
R01	2025-08-31 09:00:00	t5

Expected output

One row per qualifying user (only the earliest qualifying 24-hour window per user).

net_exposure_cents	num_ach_credits_in_window	num_returns_within_5d	user_id	window_end	window_start
100000	3	1	1	2025-09-01 09:30:00	2025-08-31 09:30:00

Hints

Use a window frame like RANGE BETWEEN INTERVAL '24 hours' PRECEDING AND CURRENT ROW to compute rolling counts/sums.
Compute an earliest_return_ts in the same rolling frame using MIN(CASE WHEN ... THEN returned_at END).
After finding the earliest qualifying window per user, compute debits to subtract using a correlated subquery bounded by window_start and earliest_return_ts.
Use PostgreSQL TO_CHAR for the formatted rolling window boundaries.

Top device per user by returned ACH credits in the last 30 days (login attribution)

You are given transactions, ach_returns, logins, and devices.

As of 2025-09-01, find the top device per user ranked by the count of returned ACH credit transactions in the last 30 days window:

Time window is 2025-08-02 00:00:00 through 2025-09-01 23:59:59 (inclusive).
A transaction counts if:
1. It is an ACH credit with status = 'posted'.
2. The transaction created_at is within the window.
3. It has a matching row in ach_returns where returned_at is also within the same window.
Attribute each counted credit to the device from the user’s most recent login within 60 minutes before the credit’s created_at (i.e., login timestamp ts satisfies created_at - 60 minutes <= ts <= created_at).

Output one row per user who has at least one counted returned credit in the window:

user_id, device_id, fingerprint, returned_count_30d

Tie-breaking: if multiple devices have the same returned_count_30d for a user, pick the smallest device_id.

Table `transactions`

column	type	constraints
txn_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
direction	VARCHAR(10)	NOT NULL
method	VARCHAR(10)	NOT NULL
amount_cents	INT	NOT NULL
created_at	TIMESTAMP	NOT NULL
status	VARCHAR(20)	NOT NULL

Sample data:

amount_cents	created_at	direction	method	status	txn_id	user_id
50000	2025-08-31 10:00:00	credit	ACH	posted	t1	1
40000	2025-08-31 18:00:00	credit	ACH	posted	t2	1
30000	2025-09-01 09:30:00	credit	ACH	posted	t3	1
20000	2025-09-01 12:00:00	debit	ACH	posted	t4	1
150000	2025-08-30 02:00:00	credit	ACH	posted	t5	2
150000	2025-09-01 02:30:00	credit	ACH	posted	t6	2
70000	2025-09-01 11:00:00	credit	CARD	posted	t7	3

Table `ach_returns`

column	type	constraints
txn_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
return_code	VARCHAR(10)	NOT NULL
returned_at	TIMESTAMP	NOT NULL

Sample data:

return_code	returned_at	txn_id
R10	2025-09-01 20:00:00	t2
R01	2025-08-31 09:00:00	t5

Table `devices`

column	type	constraints
device_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
fingerprint	VARCHAR(50)	NOT NULL
first_seen_at	TIMESTAMP	NOT NULL

Sample data:

device_id	fingerprint	first_seen_at	user_id
d1	abc123	2025-08-01 09:00:00	1
d2	abc124	2025-08-31 17:30:00	1
d3	zyx999	2025-08-29 22:00:00	2
d4	abc123	2025-09-01 10:45:00	3

Table `logins`

column	type	constraints
login_id	VARCHAR(10)	PRIMARY KEY, NOT NULL
user_id	INT	NOT NULL
device_id	VARCHAR(10)	NOT NULL
ip	VARCHAR(45)	NOT NULL
ts	TIMESTAMP	NOT NULL

Sample data:

device_id	ip	login_id	ts	user_id
d1	1.1.1.1	l1	2025-08-31 09:50:00	1
d2	1.1.1.1	l2	2025-08-31 17:40:00	1
d1	2.2.2.2	l3	2025-09-01 09:25:00	1
d3	3.3.3.3	l6	2025-08-30 01:50:00	2
d3	3.3.3.3	l4	2025-09-01 02:25:00	2
d4	4.4.4.4	l5	2025-09-01 10:50:00	3

Expected output

One row per user with at least one returned ACH credit in the window; shows the device with the highest returned-credit count (ties broken by smallest device_id).

device_id	fingerprint	returned_count_30d	user_id
d2	abc124	1	1
d3	zyx999	1	2

Hints

First filter to returned ACH credits where both the credit timestamp and the return timestamp fall inside the 30-day interval.
Use a LATERAL join (or a correlated subquery) to pick the most recent login within 60 minutes before each credit.
Aggregate counts by (user_id, device_id), then rank devices per user with ROW_NUMBER().

Earliest suspicious 24-hour ACH credit burst with returns and net exposure

Table transactions

Table ach_returns

Expected output

Hints

Top device per user by returned ACH credits in the last 30 days (login attribution)

Table transactions

Table ach_returns

Table devices

Table logins

Expected output

Hints

Table `transactions`

Table `ach_returns`

Table `transactions`

Table `ach_returns`

Table `devices`

Table `logins`