Deduplicate Transactional Records | Dataford Interview Questions

How would you write a query to identify and remove duplicate records from a transactional table?

Assume records are duplicates when they share the same transaction reference, account, date, amount, and status. Retain the record with the smallest transaction ID and remove later copies.

Output

Return each deleted duplicate record with its transaction ID and duplicate-defining fields.
Include only records removed by the query. No specific output ordering is required.

Schema

transactions

Column	Type	Description
transaction_idPK	INTEGER	Unique identifier for the stored transaction record
transaction_ref	VARCHAR(30)	Source transaction reference
account_id	INTEGER	Account associated with the transaction
transaction_date	DATE	Business date of the transaction
amount	NUMERIC(12,2)	Transaction amount
status	VARCHAR(20)	Processing status
created_at	TIMESTAMP	Timestamp when the record entered the table

Your question is Deduplicate Transactional Records. 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