Finding Duplicate Records in SQL | Dataford Interview Questions

Smc³ CarrierConnect receives transaction feeds that may contain repeated posted transactions. Write a PostgreSQL query to identify duplicate transaction records for reconciliation.

A transaction is considered a duplicate when another posted transaction has the same customer_id, merchant_id, transaction_date, amount, and currency. The unique transaction_id is not part of the duplicate definition. Treat matching NULL merchant IDs as the same value.

Requirements

Consider only transactions where status = 'posted'.
Return every transaction belonging to a duplicate group, along with the number of records in that group.
Include the merchant name when available, retain transactions whose merchant ID is NULL or has no matching merchant, and order results by customer_id, transaction_date, and transaction_id.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
merchant_id	INT	Merchant identifier
transaction_date	DATE	Date the transaction was recorded
amount	NUMERIC(12,2)	Transaction amount
currency	VARCHAR(3)	ISO currency code
status	VARCHAR(20)	Transaction processing status

merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
merchant_name	VARCHAR(100)	Merchant display name

Your question is Finding Duplicate Records in SQL. 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