Remove Duplicate Leads in Meta Ads | Dataford Interview Questions

Your question is Remove Duplicate Leads in Meta Ads. Start with the requirements and the two tables 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

Meta's business analytics team is reviewing lead data captured from Meta Lead Ads. Some leads were submitted multiple times for the same ad, and you need to identify those duplicate records and show how you would remove them while keeping one record per duplicate group.

Write a SQL query to find duplicate lead submissions, where a duplicate is defined as the same ad, email, and submission date. Keep the earliest lead_id as the canonical record.

Requirements

Return only duplicate records from lead_submissions.
For each duplicate group, show the campaign name from ad_campaigns.
Mark which row should be kept and which rows should be removed using ROW_NUMBER().
Order the output by campaign_name, submission_date, email, and lead_id.

Schema

lead_submissions

Column	Type	Description
lead_idPK	INT	Unique lead submission identifier
ad_id	INT	Ad identifier associated with the lead
email	VARCHAR(255)	Lead email address
full_name	VARCHAR(255)	Lead full name
submission_date	DATE	Date the lead was submitted
source_surface	VARCHAR(100)	Meta surface where the lead was captured

ad_campaigns

Column	Type	Description
ad_idPK	INT	Unique ad identifier
campaign_name	VARCHAR(255)	Campaign name for the ad
objective	VARCHAR(100)	Campaign objective