SQL for Duplicate Customer Removal | Dataford Interview Questions

Write a SQL query to identify and remove duplicate customer records from a primary transaction table.

Use customer_records and transactions. Treat non-null email addresses as duplicate identifiers after trimming whitespace and applying case-insensitive matching. Keep the record with the earliest created_at, using the lowest customer_id as the tie-breaker, reassign related transactions to the retained record, and remove the duplicates.

Output

Return one row per removed customer with removed_customer_id, email, full_name, and created_at.
Order by removed_customer_id ascending.

Schema

customer_records

Column	Type	Description
customer_idPK	INT	Unique customer record identifier
email	VARCHAR(255)	Customer email address used for duplicate matching
full_name	VARCHAR(150)	Customer's full name
created_at	TIMESTAMP	Timestamp when the customer record was created

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_status	VARCHAR(30)	Current transaction status
amount	NUMERIC(10,2)	Transaction amount

Your question is SQL for Duplicate Customer Removal. 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