Group By and Having for Duplicates | Dataford Interview Questions

Your question is Group By and Having for Duplicates. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Systems Planning and Analysis needs to audit records in its transaction ledger for duplicate business events received from multiple ingestion sources. Write a PostgreSQL query that identifies duplicate transaction groups without relying on the unique transaction_id.

Requirements

Define duplicates as rows sharing account_number, transaction_date, merchant_code, amount, and currency.
Use GROUP BY and HAVING COUNT(*) > 1 to find duplicate groups.
Return the duplicate count, sorted transaction IDs, and distinct source names. Preserve duplicate records whose source_id has no matching source record.
Treat NULL merchant_code values as equal for duplicate detection.

Schema

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction record ID
account_number	VARCHAR(20)	SPA ledger account reference
transaction_date	DATE	Business transaction date
merchant_code	VARCHAR(20)	Merchant identifier
amount	NUMERIC(12,2)	Transaction amount
currency	VARCHAR(3)	ISO currency code
source_id	INT	Ingestion source reference

transaction_sources

Column	Type	Description
source_idPK	INT	Source identifier
source_name	VARCHAR(80)	Ingestion source name