Find Duplicate Municipal Transactions | Dataford Interview Questions

Write a query using window functions to identify duplicate records within a municipal transactions table.

Treat records as duplicates when all transaction attributes except transaction_id match, including NULL values. Return only records belonging to duplicate groups.

Output

One row per duplicate transaction, with transaction_id, identifying attributes, duplicate_count, and duplicate_rank
Rank duplicates by ascending transaction_id within each duplicate group
Order by transaction date, department, vendor, amount, description, and transaction ID

Schema

municipal_transactions

Column	Type	Description
transaction_idPK	INT	Unique identifier for the transaction record
department	VARCHAR(100)	Municipal department responsible for the transaction
vendor_id	VARCHAR(30)	Vendor identifier associated with the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	DECIMAL(12,2)	Transaction amount in U.S. dollars
description	VARCHAR(200)	Short description of the transaction

Your question is Find Duplicate Municipal Transactions. 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