Duplicate Detection Query | Dataford Interview Questions

Write a query to identify duplicates in a large dataset while optimizing for performance.

Treat records with the same source_system, trade_reference, trade_date, and notional as duplicates. NULL values within these columns should be treated as equal duplicate-key values.

Output

One row per duplicated key combination.
Return source_system, trade_reference, trade_date, notional, and duplicate_count.
Sort by duplicate_count descending, then the key columns ascending with NULL values last.

Schema

trade_events

Column	Type	Description
event_idPK	INT	Unique trade event identifier
source_system	VARCHAR(40)	System that supplied the trade event
trade_reference	VARCHAR(40)	Source trade reference
trade_date	DATE	Trade business date
notional	NUMERIC(18,2)	Trade notional amount

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