Remove Duplicates Keeping Latest | Dataford Interview Questions

Write a SQL query to remove duplicates while keeping the latest record.

Treat rows with the same email as duplicates. Keep the row with the greatest updated_at; if timestamps tie, keep the row with the greatest contact_id. Rows with a NULL email are considered duplicates of one another.

Output

Return each deleted row with contact_id, email, and updated_at.
Order deleted rows by email ascending with NULL values last, then updated_at descending and contact_id descending.

Schema

customer_contacts

Column	Type	Description
contact_idPK	INT	Unique contact record identifier
email	VARCHAR(255)	Contact email address used to identify duplicates
contact_name	VARCHAR(150)	Contact display name
updated_at	TIMESTAMP	Time when the contact record was last updated

Your question is Remove Duplicates Keeping Latest. 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 remove duplicates while keeping the latest record.

Output

Return each deleted row with contact_id, email, and updated_at.
Order deleted rows by email ascending with NULL values last, then updated_at descending and contact_id descending.

Schema

customer_contacts

Column	Type	Description
contact_idPK	INT	Unique contact record identifier
email	VARCHAR(255)	Contact email address used to identify duplicates
contact_name	VARCHAR(150)	Contact display name
updated_at	TIMESTAMP	Time when the contact record was last updated

Your question is Remove Duplicates Keeping Latest. 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.