Remove Duplicate Customer Leads | Dataford Interview Questions

Your question is Remove Duplicate Customer Leads. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Nimbus CRM stores inbound sales leads, but duplicate rows can appear because the source system does not provide a reliable unique business key. You need to identify duplicate lead records and determine which rows should be removed.

Write a SQL query to return the duplicate rows that should be deleted, keeping only the earliest created_at row within each duplicate group.

Requirements

Treat rows as duplicates when full_name, email, phone, and source_channel are all the same.
For each duplicate group, keep the earliest created_at row.
Return only the rows that should be removed.
Include the duplicate rank within each group and order the output by duplicate fields, then created_at.

Schema

customer_leads

Column	Type	Description
lead_row_idPK	INT	Physical row identifier for each stored lead record
full_name	VARCHAR(100)	Lead full name
email	VARCHAR(150)	Lead email address
phone	VARCHAR(30)	Lead phone number
source_channel	VARCHAR(50)	Channel where the lead originated
created_at	TIMESTAMP	Timestamp when the row was inserted

Your question is Remove Duplicate Customer Leads. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Write a SQL query to return the duplicate rows that should be deleted, keeping only the earliest created_at row within each duplicate group.

Requirements

Treat rows as duplicates when full_name, email, phone, and source_channel are all the same.
For each duplicate group, keep the earliest created_at row.
Return only the rows that should be removed.
Include the duplicate rank within each group and order the output by duplicate fields, then created_at.

Schema

customer_leads

Column	Type	Description
lead_row_idPK	INT	Physical row identifier for each stored lead record
full_name	VARCHAR(100)	Lead full name
email	VARCHAR(150)	Lead email address
phone	VARCHAR(30)	Lead phone number
source_channel	VARCHAR(50)	Channel where the lead originated
created_at	TIMESTAMP	Timestamp when the row was inserted