SQL for Fraudulent Transactions | Dataford Interview Questions

Write a SQL query to find fraudulent transactions where the average gap between two transactions is greater than 2 days.

Use the transactions table. Treat an account as fraudulent when the average elapsed time between its consecutive transactions exceeds two days. Return every transaction belonging to those accounts.

Output

One row per qualifying transaction with account_id, transaction_id, transaction_timestamp, and average_gap_days.
Include only accounts with an average gap greater than 2 days.
Order by account_id, transaction_timestamp, and transaction_id.

Schema

transactions

Column	Type	Description
transaction_idPK	VARCHAR(20)	Unique transaction identifier
account_id	VARCHAR(20)	Account associated with the transaction
transaction_timestamp	TIMESTAMPTZ	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount

Your question is SQL for Fraudulent Transactions. Start with the requirements and the one table 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