Detect Fraud Patterns in SQL | Dataford Interview Questions

Write an SQL query to identify recurring fraud patterns by detecting anomalous transaction sequences within short time intervals.

Treat a transaction as anomalous when it occurs no more than 10 minutes after the previous transaction for the same account. Return accounts with at least two anomalous transactions.

Output

One row per qualifying account
Columns: account_id, account_name, anomalous_transaction_count, first_anomalous_at, and last_anomalous_at
Sort by anomalous transaction count descending, then account_id ascending

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(100)	Account holder name
account_status	VARCHAR(20)	Current account status

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
transaction_time	TIMESTAMP	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount
merchant_category	VARCHAR(50)	Merchant category for the transaction

Your question is Detect Fraud Patterns in SQL. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem