SQL Duplicate Record Detection | Dataford Interview Questions

Write a SQL query to identify duplicate records in a user-transaction table.

Treat records as duplicates when user_id, transaction_date, amount, transaction_type, and payment_method match. The unique transaction ID and notes do not determine duplication.

Output

Return one row per duplicate group with user_id, transaction_date, amount, transaction_type, payment_method, duplicate_count, and duplicate_record_ids.
Include only groups containing at least two records.
Order by user_id, transaction_date, and transaction_type.

Schema

user_transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for the transaction record
user_id	INT	Identifier of the user associated with the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(10,2)	Transaction amount
transaction_type	VARCHAR(30)	Category of transaction
payment_method	VARCHAR(20)	Method used to complete the transaction
notes	TEXT	自由-form operational notes that do not define duplication

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