Find Duplicate Policy Records | Dataford Interview Questions

Write a SQL query to find duplicate records in an insurance policy database.

Treat records as duplicates when they share the same policy number, policy type, effective date, and policyholder. Use the policies and policyholders tables. Include records whose policyholder reference is missing or unmatched.

Output

One row per duplicate policy record.
Columns: policy_id, policy_number, policyholder_name, policy_type, effective_date, and duplicate_count.
Include only records belonging to duplicate groups. Order by policy_number, then policy_id ascending.

Schema

policies

Column	Type	Description
policy_idPK	INT	Unique policy record identifier
policy_number	VARCHAR(20)	Business policy number
policyholder_id	INT	Referenced policyholder identifier
policy_type	VARCHAR(30)	Type of insurance policy
effective_date	DATE	Date coverage becomes effective

policyholders

Column	Type	Description
policyholder_idPK	INT	Unique policyholder identifier
policyholder_name	VARCHAR(100)	Full name of the policyholder

Your question is Find Duplicate Policy Records. Start with the requirements and the two tables 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