Deduplicate Policy Records | Dataford Interview Questions

Write a SQL query to identify duplicate policy records within a database and retain only the most recently updated entry.

Treat records with the same policy_number as duplicates. If update timestamps are tied, retain the record with the greatest policy_id. Do not include older duplicate records.

Output

One row per retained policy number, with columns policy_id, policy_number, policyholder_name, policy_status, premium_amount, and updated_at.
Include the most recently updated record for every policy number, including policy numbers without duplicates.
Order by policy_number ascending.

Schema

policies

Column	Type	Description
policy_idPK	INTEGER	Unique identifier for a policy record version
policy_number	VARCHAR(30)	Business identifier used to detect duplicate policy records
policyholder_name	VARCHAR(100)	Name of the policyholder
policy_status	VARCHAR(20)	Current status recorded for the policy version
premium_amount	NUMERIC(12,2)	Policy premium amount
updated_at	TIMESTAMP	Timestamp when the policy record was last updated

Your question is Deduplicate Policy Records. 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