Detect Duplicate Payments | Dataford Interview Questions

Given a table of user transactions, write a query to detect potential duplicate payments, defined as transactions by the same user for the same amount within a 5-minute window.

Return each qualifying transaction pair once. Exclude pairs with different users, different amounts, missing comparison values, or time gaps greater than five minutes.

Output

One row per qualifying transaction pair with user_id, both transaction IDs, both timestamps, amount, and elapsed_seconds.
Order by user_id, first timestamp, second timestamp, and transaction IDs.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
user_id	INT	Identifier of the user who made the payment
amount	NUMERIC(12,2)	Payment amount
transaction_ts	TIMESTAMPTZ	Timestamp when the payment occurred

Your question is Detect Duplicate Payments. 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