Remove Duplicate Customer Leads

Task

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.

Table Definition

customer_leads

column	type	description
lead_row_id	INT	Physical row identifier used only for cleanup output
full_name	VARCHAR(100)	Lead full name
email	VARCHAR(150)	Lead email address
phone	VARCHAR(30)	Lead phone number
source_channel	VARCHAR(50)	Acquisition source
created_at	TIMESTAMP	Time the row was inserted

Sample Data

lead_row_id	full_name	email	phone	source_channel	created_at
108	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:00:00
101	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:15:00
110	Noah Kim		555-0103	referral	2024-02-04 08:30:00
103	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:18:00
105	Ava Singh	ava@example.com	NULL	partner	2024-02-02 11:00:00
102	Emma Ross	emma@example.com	555-0102	ad	2024-02-01 10:00:00
111	Noah Kim		555-0103	referral	2024-02-04 08:45:00
104	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:20:00

Expected Output

lead_row_id	full_name	email	phone	source_channel	created_at	duplicate_rank
103	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:18:00	2
104	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:20:00	3
106	Ava Singh	ava@example.com	NULL	partner	2024-02-02 11:05:00	2
109	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:05:00	2
111	Noah Kim		555-0103	referral	2024-02-04 08:45:00	2

Task

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.

Table Definition

customer_leads

column	type	description
lead_row_id	INT	Physical row identifier used only for cleanup output
full_name	VARCHAR(100)	Lead full name
email	VARCHAR(150)	Lead email address
phone	VARCHAR(30)	Lead phone number
source_channel	VARCHAR(50)	Acquisition source
created_at	TIMESTAMP	Time the row was inserted

Sample Data

lead_row_id	full_name	email	phone	source_channel	created_at
108	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:00:00
101	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:15:00
110	Noah Kim		555-0103	referral	2024-02-04 08:30:00
103	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:18:00
105	Ava Singh	ava@example.com	NULL	partner	2024-02-02 11:00:00
102	Emma Ross	emma@example.com	555-0102	ad	2024-02-01 10:00:00
111	Noah Kim		555-0103	referral	2024-02-04 08:45:00
104	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:20:00

Expected Output

lead_row_id	full_name	email	phone	source_channel	created_at	duplicate_rank
103	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:18:00	2
104	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:20:00	3
106	Ava Singh	ava@example.com	NULL	partner	2024-02-02 11:05:00	2
109	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:05:00	2
111	Noah Kim		555-0103	referral	2024-02-04 08:45:00	2

Task

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.

Table Definition

customer_leads

column	type	description
lead_row_id	INT	Physical row identifier used only for cleanup output
full_name	VARCHAR(100)	Lead full name
email	VARCHAR(150)	Lead email address
phone	VARCHAR(30)	Lead phone number
source_channel	VARCHAR(50)	Acquisition source
created_at	TIMESTAMP	Time the row was inserted

Sample Data

lead_row_id	full_name	email	phone	source_channel	created_at
108	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:00:00
101	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:15:00
110	Noah Kim		555-0103	referral	2024-02-04 08:30:00
103	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:18:00
105	Ava Singh	ava@example.com	NULL	partner	2024-02-02 11:00:00
102	Emma Ross	emma@example.com	555-0102	ad	2024-02-01 10:00:00
111	Noah Kim		555-0103	referral	2024-02-04 08:45:00
104	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:20:00

Expected Output

lead_row_id	full_name	email	phone	source_channel	created_at	duplicate_rank
103	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:18:00	2
104	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:20:00	3
106	Ava Singh	ava@example.com	NULL	partner	2024-02-02 11:05:00	2
109	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:05:00	2
111	Noah Kim		555-0103	referral	2024-02-04 08:45:00	2

Task

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.

Table Definition

customer_leads

column	type	description
lead_row_id	INT	Physical row identifier used only for cleanup output
full_name	VARCHAR(100)	Lead full name
email	VARCHAR(150)	Lead email address
phone	VARCHAR(30)	Lead phone number
source_channel	VARCHAR(50)	Acquisition source
created_at	TIMESTAMP	Time the row was inserted

Sample Data

lead_row_id	full_name	email	phone	source_channel	created_at
108	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:00:00
101	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:15:00
110	Noah Kim		555-0103	referral	2024-02-04 08:30:00
103	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:18:00
105	Ava Singh	ava@example.com	NULL	partner	2024-02-02 11:00:00
102	Emma Ross	emma@example.com	555-0102	ad	2024-02-01 10:00:00
111	Noah Kim		555-0103	referral	2024-02-04 08:45:00
104	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:20:00

Expected Output

lead_row_id	full_name	email	phone	source_channel	created_at	duplicate_rank
103	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:18:00	2
104	Liam Patel	liam@example.com	555-0100	web	2024-02-01 09:20:00	3
106	Ava Singh	ava@example.com	NULL	partner	2024-02-02 11:05:00	2
109	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:05:00	2
111	Noah Kim		555-0103	referral	2024-02-04 08:45:00	2

Interview Guides

Task

Requirements

Table Definition

Sample Data

Expected Output

Remove Duplicate Customer Leads

Task

Requirements

Table Definition

Sample Data

Expected Output

Practice SQL

Remove Duplicate Customer Leads

Task

Requirements

Table Definition

Sample Data

Expected Output

Remove Duplicate Customer Leads

Task

Requirements

Table Definition

Sample Data

Expected Output

Practice SQL