De-duplicate Without Primary Key | Dataford Interview Questions

How would you identify and remove duplicate customer records from a table without a primary key at The Home Depot?

Assume duplicate rows are defined by matching email, first_name, last_name, and phone_number. Write a SQL query to keep one row per duplicate set and remove the rest.

Output

Return the rows that should remain after deduplication, one row per unique customer
Include all customer columns in the result
If duplicates exist, keep the earliest created_at row, and break any ties by the smallest ctid
Sort by email, then created_at, then ctid

Schema

customers

Column	Type	Description
customer_id	BIGINT	Surrogate-style identifier present in the source, but not guaranteed to be unique
first_name	VARCHAR(100)	Customer first name
last_name	VARCHAR(100)	Customer last name
email	VARCHAR(255)	Customer email address
phone_number	VARCHAR(30)	Customer phone number
created_at	TIMESTAMP	Record creation timestamp

Your question is De-duplicate Without Primary Key. 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