SQL Duplicate Detection and Optimization | Dataford Interview Questions

Write a SQL query to identify duplicate records in a large transactional dataset and explain how you would optimize it.

Assume a duplicate has the same source system, account, transaction date, transaction type, and amount. Use the provided transaction and source-system tables.

Output

One row per duplicate group, including the grouping fields, source name, duplicate count, and comma-separated transaction IDs.
Include only groups containing at least two records.
Order by source system, account, transaction date, transaction type, and amount.

Schema

transaction_records

Column	Type	Description
transaction_idPK	BIGINT	Unique transaction identifier
source_system_id	INT	Identifier of the originating source system
account_id	VARCHAR(30)	Account associated with the transaction
transaction_date	DATE	Business date of the transaction
transaction_type	VARCHAR(20)	Transaction classification
amount	NUMERIC(12,2)	Transaction amount

source_systems

Column	Type	Description
source_system_idPK	INT	Unique source-system identifier
source_name	VARCHAR(80)	Readable source-system name

Your question is SQL Duplicate Detection and Optimization. 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