Dataford
Popular roles
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Browse
Browse All RolesEvery role hub, from analyst to MLBrowse All CompaniesCompany-specific interview loopsAll Interview GuidesThe full guide library
Top questions by role
Software EngineerData AnalystData ScientistData EngineerBusiness AnalystAI EngineerMachine Learning EngineerProduct Manager
Top questions by skill
SQLPythonStatisticsMachine LearningA/B TestingSystem DesignGenerative AIProduct SenseMetricsBehavioral
Browse all questions →Try a mock interview
ExperiencesCoaching
New
Practice
Mock InterviewsTimed interview simulations with feedbackSuccess PathYour 6-week structured planModulesCurated lessons by topicWebinarsTalks from ex-Big Tech data leadsPlaygroundA free-form scratch editor
Learn
BlogInterview strategy and career adviceTech Job Market ReportHiring trends across data and AI rolesFor UniversitiesDataford for career centersAbout DatafordWho we are and how we build
Pricing
Get started
Remove Duplicate Leads in Meta Ads
00:00
5 left

Remove Duplicate Leads in Meta Ads

MediumSQL · PostgreSQL

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

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

Schema

lead_submissions
ColumnTypeDescription
lead_idPKINTUnique lead submission identifier
ad_idINTAd identifier associated with the lead
emailVARCHAR(255)Lead email address
full_nameVARCHAR(255)Lead full name
submission_dateDATEDate the lead was submitted
source_surfaceVARCHAR(100)Meta surface where the lead was captured
ad_campaigns
ColumnTypeDescription
ad_idPKINTUnique ad identifier
campaign_nameVARCHAR(255)Campaign name for the ad
objectiveVARCHAR(100)Campaign objective
Tableslead_submissionsad_campaigns
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the two tables are in the Question tab.

You need to log in / sign up to run or submit.
CodePostgreSQL
Sign up free to run your codeLog inLn 1
Run your query to see results here.