Deduplicate Large Transaction Table | Dataford Interview Questions

Write a SQL query to identify and remove duplicate records from a large transactional table without using temporary tables.

Treat records with the same account_id, transaction_ts, amount, merchant_code, and status as duplicates. Preserve the record with the smallest transaction_id and return the records removed.

Output

One row per deleted duplicate.
Columns: transaction_id, account_id, transaction_ts, amount, merchant_code, and status.
Order by transaction_id ascending.

Schema

transactions

Column	Type	Description
transaction_idPK	BIGINT	Unique transaction identifier
account_id	BIGINT	Account associated with the transaction
transaction_ts	TIMESTAMPTZ	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount
merchant_code	VARCHAR(20)	Merchant identifier
status	VARCHAR(20)	Transaction processing status

Your question is Deduplicate Large Transaction Table. 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

Write a SQL query to identify and remove duplicate records from a large transactional table without using temporary tables.

Output

One row per deleted duplicate.
Columns: transaction_id, account_id, transaction_ts, amount, merchant_code, and status.
Order by transaction_id ascending.

Schema

transactions

Column	Type	Description
transaction_idPK	BIGINT	Unique transaction identifier
account_id	BIGINT	Account associated with the transaction
transaction_ts	TIMESTAMPTZ	Timestamp when the transaction occurred
amount	NUMERIC(12,2)	Transaction amount
merchant_code	VARCHAR(20)	Merchant identifier
status	VARCHAR(20)	Transaction processing status

Your question is Deduplicate Large Transaction Table. 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.