Write SQL/Python for ACH fraud analytics
Problem Description
You are tasked with analyzing financial transaction data to identify potential ACH fraud patterns. The analysis involves a combination of SQL queries for historical data and a Python component for real-time streaming data processing. All date-based queries should consider data as of 2025-09-01.
Schema
The following tables are available:
- 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)
Tasks
Task A: SQL - Rolling Window Analysis
For each user, identify the earliest rolling 24-hour window that ends on or before 2025-09-01 23:59:59, satisfying the following conditions:
- The user has at least 3 ACH credit transactions within this 24-hour window.
- At least one of these ACH credit transactions is returned within 5 days of its
created_attimestamp.
For each such window, calculate the net_exposure_cents. This is defined as the sum of amount_cents for all ACH credits within that 24-hour window, minus the sum of amount_cents for any ACH debits by the same user that occurred between the window_start and the earliest returned_at timestamp among the returned credits in that window.
Output columns: user_id, window_start, window_end, num_ach_credits_in_window, num_returns_within_5d, net_exposure_cents.
Use only the transactions and ach_returns tables.
Task B: SQL - Device Ranking for Fraud
As of 2025-09-01, for each user, rank their devices based on the count of returned ACH credits linked to that device within the last 30 days (i.e., credits with created_at between 2025-08-02 and 2025-09-01, whose txn_id appears in ach_returns with returned_at also within the same 30-day interval). A device is linked to a credit if it was the most recent device used for a login by that user within 60 minutes before the credit's created_at timestamp.
Output the top-ranked device per user with the following columns: user_id, device_id, fingerprint, returned_count_30d.
Task C: Python - Streaming Fingerprint Clustering
You are given a stream of logins events, which may arrive out of order by up to 5 minutes. Implement a system to collapse device fingerprints that differ by exactly one character (case-sensitive Hamming distance of 1) into a canonical fingerprint. This can be conceptualized as a union-find problem over fingerprints.
Identify and return all clusters where a canonical fingerprint is used by at least 3 distinct users within any 7-day rolling window ending on 2025-09-01.
Output: A list of tuples (canonical_fingerprint, window_start, window_end, distinct_user_count).
Additionally, state the time and space complexity of your solution and describe how you handle late events and ties (e.g., for canonical fingerprint selection or window boundaries).
AI interview prep
Core dilemma
Accurately correlating disparate temporal events and fuzzy data points across batch (SQL) and streaming (Python) contexts to detect complex fraud patterns, while managing data freshness, latency, and computational efficiency.
3 tradeoffs and 2 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Write SQL/Python for ACH fraud analytics
Problem Description
You are tasked with analyzing financial transaction data to identify potential ACH fraud patterns. The analysis involves a combination of SQL queries for historical data and a Python component for real-time streaming data processing. All date-based queries should consider data as of 2025-09-01.
Schema
The following tables are available:
- 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)
Tasks
Task A: SQL - Rolling Window Analysis
For each user, identify the earliest rolling 24-hour window that ends on or before 2025-09-01 23:59:59, satisfying the following conditions:
- The user has at least 3 ACH credit transactions within this 24-hour window.
- At least one of these ACH credit transactions is returned within 5 days of its
created_attimestamp.
For each such window, calculate the net_exposure_cents. This is defined as the sum of amount_cents for all ACH credits within that 24-hour window, minus the sum of amount_cents for any ACH debits by the same user that occurred between the window_start and the earliest returned_at timestamp among the returned credits in that window.
Output columns: user_id, window_start, window_end, num_ach_credits_in_window, num_returns_within_5d, net_exposure_cents.
Use only the transactions and ach_returns tables.
Task B: SQL - Device Ranking for Fraud
As of 2025-09-01, for each user, rank their devices based on the count of returned ACH credits linked to that device within the last 30 days (i.e., credits with created_at between 2025-08-02 and 2025-09-01, whose txn_id appears in ach_returns with returned_at also within the same 30-day interval). A device is linked to a credit if it was the most recent device used for a login by that user within 60 minutes before the credit's created_at timestamp.
Output the top-ranked device per user with the following columns: user_id, device_id, fingerprint, returned_count_30d.
Task C: Python - Streaming Fingerprint Clustering
You are given a stream of logins events, which may arrive out of order by up to 5 minutes. Implement a system to collapse device fingerprints that differ by exactly one character (case-sensitive Hamming distance of 1) into a canonical fingerprint. This can be conceptualized as a union-find problem over fingerprints.
Identify and return all clusters where a canonical fingerprint is used by at least 3 distinct users within any 7-day rolling window ending on 2025-09-01.
Output: A list of tuples (canonical_fingerprint, window_start, window_end, distinct_user_count).
Additionally, state the time and space complexity of your solution and describe how you handle late events and ties (e.g., for canonical fingerprint selection or window boundaries).