Interview Guides

Remove Duplicate Customer Leads | Dataford Interview Questions - Dataford - Ace your Interview

Remove Duplicate Customer Leads

Medium

SQL & Data Manipulation

Asked at 1 company1Window FunctionsCTEsData Wrangling

Also asked at

Problem

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

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

customer_leads

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		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
107	Ava Singh	ava@example.com	555-9999	partner	2024-02-02 11:10:00
106	Ava Singh	ava@example.com		partner	2024-02-02 11:05:00
109	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:05:00
112	Owen Lee		555-0104	web	2024-02-05 12:00:00
113	Maya Chen	null	555-0103	web	2024-01-31 07:21:42
114	Liam Patel	liam@example.com	555-0100	web	2024-01-30 10:14:04
115	Emma Ross	liam@example.com	555-9999	web	2024-02-02 01:47:14
116	Liam Patel	ava@example.com	555-0100	referral	2024-01-30 16:04:56
117	Ava Singh	emma@example.com	555-0102	web	2024-02-01 00:28:44
118	Maya Chen	ava@example.com	555-9999	partner	2024-02-06 00:15:37
119	Ava Singh	maya@example.com	555-9999	web	2024-01-30 12:27:12
120	Ava Singh	ava@example.com	555-0100	partner	2024-02-03 16:48:47
121	Liam Patel	maya@example.com	555-0104	web	2024-02-07 20:13:41
122	Liam Patel	ava@example.com	555-0100	web	2024-02-03 18:17:26
123	Liam Patel	ava@example.com	555-0100	partner	2024-02-03 14:57:41
124	Emma Ross	liam@example.com	555-9999	partner	2024-02-03 02:33:27
125	Emma Ross	ava@example.com	555-9999	ad	2024-02-05 10:41:38

Expected Output

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

Problem

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

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

customer_leads

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		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
107	Ava Singh	ava@example.com	555-9999	partner	2024-02-02 11:10:00
106	Ava Singh	ava@example.com		partner	2024-02-02 11:05:00
109	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:05:00
112	Owen Lee		555-0104	web	2024-02-05 12:00:00
113	Maya Chen	null	555-0103	web	2024-01-31 07:21:42
114	Liam Patel	liam@example.com	555-0100	web	2024-01-30 10:14:04
115	Emma Ross	liam@example.com	555-9999	web	2024-02-02 01:47:14
116	Liam Patel	ava@example.com	555-0100	referral	2024-01-30 16:04:56
117	Ava Singh	emma@example.com	555-0102	web	2024-02-01 00:28:44
118	Maya Chen	ava@example.com	555-9999	partner	2024-02-06 00:15:37
119	Ava Singh	maya@example.com	555-9999	web	2024-01-30 12:27:12
120	Ava Singh	ava@example.com	555-0100	partner	2024-02-03 16:48:47
121	Liam Patel	maya@example.com	555-0104	web	2024-02-07 20:13:41
122	Liam Patel	ava@example.com	555-0100	web	2024-02-03 18:17:26
123	Liam Patel	ava@example.com	555-0100	partner	2024-02-03 14:57:41
124	Emma Ross	liam@example.com	555-9999	partner	2024-02-03 02:33:27
125	Emma Ross	ava@example.com	555-9999	ad	2024-02-05 10:41:38

Expected Output

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

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Remove Duplicate Leads in Meta AdsMedium

Flag and Remove Duplicate CustomersMedium

Deduplicate Meta Lead Form SubmissionsMedium

Next question

Remove Duplicate Customer Leads

Medium

SQL & Data Manipulation

Asked at 1 company1Window FunctionsCTEsData Wrangling

Also asked at

Problem

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

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

customer_leads

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		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
107	Ava Singh	ava@example.com	555-9999	partner	2024-02-02 11:10:00
106	Ava Singh	ava@example.com		partner	2024-02-02 11:05:00
109	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:05:00
112	Owen Lee		555-0104	web	2024-02-05 12:00:00
113	Maya Chen	null	555-0103	web	2024-01-31 07:21:42
114	Liam Patel	liam@example.com	555-0100	web	2024-01-30 10:14:04
115	Emma Ross	liam@example.com	555-9999	web	2024-02-02 01:47:14
116	Liam Patel	ava@example.com	555-0100	referral	2024-01-30 16:04:56
117	Ava Singh	emma@example.com	555-0102	web	2024-02-01 00:28:44
118	Maya Chen	ava@example.com	555-9999	partner	2024-02-06 00:15:37
119	Ava Singh	maya@example.com	555-9999	web	2024-01-30 12:27:12
120	Ava Singh	ava@example.com	555-0100	partner	2024-02-03 16:48:47
121	Liam Patel	maya@example.com	555-0104	web	2024-02-07 20:13:41
122	Liam Patel	ava@example.com	555-0100	web	2024-02-03 18:17:26
123	Liam Patel	ava@example.com	555-0100	partner	2024-02-03 14:57:41
124	Emma Ross	liam@example.com	555-9999	partner	2024-02-03 02:33:27
125	Emma Ross	ava@example.com	555-9999	ad	2024-02-05 10:41:38

Expected Output

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

Problem

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

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

customer_leads

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		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
107	Ava Singh	ava@example.com	555-9999	partner	2024-02-02 11:10:00
106	Ava Singh	ava@example.com		partner	2024-02-02 11:05:00
109	Maya Chen	maya@example.com	555-0101	web	2024-02-03 09:05:00
112	Owen Lee		555-0104	web	2024-02-05 12:00:00
113	Maya Chen	null	555-0103	web	2024-01-31 07:21:42
114	Liam Patel	liam@example.com	555-0100	web	2024-01-30 10:14:04
115	Emma Ross	liam@example.com	555-9999	web	2024-02-02 01:47:14
116	Liam Patel	ava@example.com	555-0100	referral	2024-01-30 16:04:56
117	Ava Singh	emma@example.com	555-0102	web	2024-02-01 00:28:44
118	Maya Chen	ava@example.com	555-9999	partner	2024-02-06 00:15:37
119	Ava Singh	maya@example.com	555-9999	web	2024-01-30 12:27:12
120	Ava Singh	ava@example.com	555-0100	partner	2024-02-03 16:48:47
121	Liam Patel	maya@example.com	555-0104	web	2024-02-07 20:13:41
122	Liam Patel	ava@example.com	555-0100	web	2024-02-03 18:17:26
123	Liam Patel	ava@example.com	555-0100	partner	2024-02-03 14:57:41
124	Emma Ross	liam@example.com	555-9999	partner	2024-02-03 02:33:27
125	Emma Ross	ava@example.com	555-9999	ad	2024-02-05 10:41:38

Expected Output

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

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Remove Duplicate Leads in Meta AdsMedium

Flag and Remove Duplicate CustomersMedium

Deduplicate Meta Lead Form SubmissionsMedium

Next question