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
- Treat records as duplicates when they share the same
lead_id and form_id.
- Preserve the earliest valid submission by ranking rows using
submitted_at ascending, then ingested_at ascending, then submission_id ascending.
- Return only rows that should be deleted, along with campaign and advertiser context.
- 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.