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
Deduplicate Meta Lead Form Submissions
00:00
5 left

Deduplicate Meta Lead Form Submissions

MediumSQL · PostgreSQL

Problem

Meta's lead ingestion pipeline for Facebook Lead Ads occasionally creates duplicate submission records for the same lead and form. Write a SQL query to identify the canonical record to keep for each duplicated lead and return only the duplicate rows that should be removed.

Requirements

  1. Treat records as duplicates when they share the same lead_id and form_id.
  2. Preserve the earliest valid submission by ranking rows using submitted_at ascending, then ingested_at ascending, then submission_id ascending.
  3. Return only rows that should be deleted, along with campaign and advertiser context.
  4. Exclude rows tied to inactive campaigns so cleanup only applies to active data.

Output details

  • Return columns in this order: submission_id, lead_id, form_id, campaign_name, advertiser_name, submitted_at, ingested_at, duplicate_rank.
  • Sort by lead_id ascending, form_id ascending, duplicate_rank ascending, then submission_id ascending.
  • Treat campaigns with status exactly Active as active.

Schema

lead_submissions
ColumnTypeDescription
submission_idPKINTUnique submission row ID
lead_idVARCHAR(20)Lead identifier from Meta lead capture
form_idVARCHAR(20)Lead form identifier
campaign_idINTAssociated ad campaign
submitted_atTIMESTAMPTime the lead was submitted
ingested_atTIMESTAMPTime the row landed in the warehouse
emailVARCHAR(100)Lead email address
phoneVARCHAR(20)Lead phone number
source_surfaceVARCHAR(30)Meta surface where the lead originated
campaigns
ColumnTypeDescription
campaign_idPKINTCampaign identifier
advertiser_idINTOwning advertiser
campaign_nameVARCHAR(100)Campaign name
statusVARCHAR(20)Campaign status
start_dateDATECampaign start date
advertisers
ColumnTypeDescription
advertiser_idPKINTAdvertiser identifier
advertiser_nameVARCHAR(100)Advertiser name
verticalVARCHAR(50)Advertiser industry
regionVARCHAR(30)Primary region
Tableslead_submissionscampaignsadvertisers
Interviewer

Whenever you're ready, walk me through your approach or go straight to the code. The requirements and the three 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.