Write SQL/Python for ACH fraud analytics
Company: Gemini
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
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:
1) The user has **at least 3** `ACH` **credit** transactions (status = `posted`) in that 24-hour window.
2) **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.
Tables
transactions(txn_id VARCHAR(10), user_id INT, direction VARCHAR(10), method VARCHAR(10), amount_cents INT, created_at TIMESTAMP, status VARCHAR(20))
ach_returns(txn_id VARCHAR(10), return_code VARCHAR(10), returned_at TIMESTAMP)
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).
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`.
Tables
transactions(txn_id VARCHAR(10), user_id INT, direction VARCHAR(10), method VARCHAR(10), amount_cents INT, created_at TIMESTAMP, status VARCHAR(20))
ach_returns(txn_id VARCHAR(10), return_code VARCHAR(10), returned_at TIMESTAMP)
devices(device_id VARCHAR(10), user_id INT, fingerprint VARCHAR(50), first_seen_at TIMESTAMP)
logins(login_id VARCHAR(10), user_id INT, device_id VARCHAR(10), ip VARCHAR(45), ts TIMESTAMP)
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.