Fraud Detection from Transactions | Dataford Interview Questions

How would you design a SQL-based approach to catch fraud using transaction data?

For this exercise, flag completed transactions that are at least three times the account's prior completed average amount or occur during an hour with at least three completed transactions for that account.

Output

One row per flagged transaction with transaction_id, account_id, account_name, occurred_at, amount, and fraud_reason.
Include only flagged completed transactions, ordered by occurred_at ascending, then transaction_id ascending.

Schema

accounts

Column	Type	Description
account_idPK	INT	Unique Robinhood customer account identifier
account_name	VARCHAR(100)	Account holder display name
risk_tier	VARCHAR(20)	Current account risk classification

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
occurred_at	TIMESTAMP	Transaction timestamp
amount	DECIMAL(12,2)	Transaction amount in dollars
status	VARCHAR(20)	Transaction processing status

Your question is Fraud Detection from Transactions. 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