SQL Duplicate Removal | Dataford Interview Questions

Write a SQL query to find and remove duplicate records from a database table.

Treat rows as duplicates when policy_number, effective_date, risk_score, and source_system are identical, including matching NULL values. Retain the row with the smallest record_id in each duplicate set and return the records removed.

Output

One row per removed duplicate, with record_id, policy_number, effective_date, risk_score, and source_system.
Sort by record_id in ascending order.

Schema

policy_risk_records

Column	Type	Description
record_idPK	INT	Unique identifier for the risk record
policy_number	VARCHAR(30)	Policy identifier supplied by the source system
effective_date	DATE	Date on which the policy risk record becomes effective
risk_score	INT	Calculated risk score
source_system	VARCHAR(30)	System that supplied the record

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

Write a SQL query to find and remove duplicate records from a database table.

Output

One row per removed duplicate, with record_id, policy_number, effective_date, risk_score, and source_system.
Sort by record_id in ascending order.

Schema

policy_risk_records

Column	Type	Description
record_idPK	INT	Unique identifier for the risk record
policy_number	VARCHAR(30)	Policy identifier supplied by the source system
effective_date	DATE	Date on which the policy risk record becomes effective
risk_score	INT	Calculated risk score
source_system	VARCHAR(30)	System that supplied the record

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