SQL: Deduplicate Latest Records | Dataford Interview Questions

Write a SQL query to find duplicate records in a customer table and keep only the latest record per customer. Use the customer_records table, treating the greatest updated_at value as latest. If timestamps tie, retain the row with the greatest record_id.

Output

Return one row per customer with record_id, customer_id, customer_name, email, updated_at, and duplicate_count.
Include customers with and without duplicates, excluding superseded records.
Order by customer_id ascending.

Schema

customer_records

Column	Type	Description
record_idPK	INT	Unique identifier for the stored customer record
customer_id	INT	Business identifier shared by records for the same customer
customer_name	VARCHAR(150)	Customer's full name
email	VARCHAR(255)	Customer email address
updated_at	TIMESTAMPTZ	Timestamp when the customer record was last updated

Your question is SQL: Deduplicate Latest 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

Output

Return one row per customer with record_id, customer_id, customer_name, email, updated_at, and duplicate_count.
Include customers with and without duplicates, excluding superseded records.
Order by customer_id ascending.

Schema

customer_records

Column	Type	Description
record_idPK	INT	Unique identifier for the stored customer record
customer_id	INT	Business identifier shared by records for the same customer
customer_name	VARCHAR(150)	Customer's full name
email	VARCHAR(255)	Customer email address
updated_at	TIMESTAMPTZ	Timestamp when the customer record was last updated

Your question is SQL: Deduplicate Latest 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.