SQL Duplicate Transaction Detection | Dataford Interview Questions

Write a SQL query to identify duplicate transactions within a specific timeframe to flag potential fraud.

For this exercise, analyze transactions from February 1 through February 28, 2025. Treat transactions as duplicates when they share the same account, merchant, amount, and timestamp. Return every transaction belonging to a duplicate set.

Output

One row per duplicate transaction.
Include transaction_id, account_id, account_name, merchant_id, amount, occurred_at, and duplicate_count.
Order by occurred_at, account_id, and transaction_id ascending.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
account_id	INT	Account associated with the transaction
merchant_id	INT	Merchant identifier
amount	NUMERIC(10,2)	Transaction amount
occurred_at	TIMESTAMP	Transaction timestamp
memo	TEXT	Optional transaction memo

accounts

Column	Type	Description
account_idPK	INT	Unique account identifier
account_name	VARCHAR(100)	Account holder name
account_type	VARCHAR(30)	Account category

Your question is SQL Duplicate Transaction Detection. 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