Duplicate Transactions in 5 Minutes | Dataford Interview Questions

Write a SQL query using window functions to identify duplicate PayPal transaction records within a five-minute window.

Treat a later record as a duplicate when it has the same payer, merchant, amount, currency, and status as the immediately preceding matching record, with a gap of no more than five minutes.

Output

Return one row per later duplicate record with transaction_id, duplicate_of_transaction_id, payer_id, merchant_id, amount, currency, status, transaction_ts, and seconds_since_previous.
Order by payer_id, transaction_ts, and transaction_id.

Schema

paypal_transactions

Column	Type	Description
transaction_idPK	INT	Unique PayPal transaction identifier
payer_id	VARCHAR(20)	Identifier of the payer account
merchant_id	VARCHAR(20)	Identifier of the merchant account
amount	NUMERIC(12,2)	Transaction amount
currency	VARCHAR(3)	ISO currency code
status	VARCHAR(20)	Transaction processing status
transaction_ts	TIMESTAMP	Timestamp when the transaction was recorded

Your question is Duplicate Transactions in 5 Minutes. 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