Your question is Deduplicate Meta Lead Form Submissions. Start with the requirements and the three 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.
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.
lead_id and form_id.submitted_at ascending, then ingested_at ascending, then submission_id ascending.| Column | Type | Description |
|---|---|---|
| submission_idPK | INT | Unique submission row ID |
| lead_id | VARCHAR(20) | Lead identifier from Meta lead capture |
| form_id | VARCHAR(20) | Lead form identifier |
| campaign_id | INT | Associated ad campaign |
| submitted_at | TIMESTAMP | Time the lead was submitted |
| ingested_at | TIMESTAMP | Time the row landed in the warehouse |
| VARCHAR(100) | Lead email address | |
| phone | VARCHAR(20) | Lead phone number |
| source_surface | VARCHAR(30) | Meta surface where the lead originated |
| Column | Type | Description |
|---|---|---|
| campaign_idPK | INT | Campaign identifier |
| advertiser_id | INT | Owning advertiser |
| campaign_name | VARCHAR(100) | Campaign name |
| status | VARCHAR(20) | Campaign status |
| start_date | DATE | Campaign start date |
| Column | Type | Description |
|---|---|---|
| advertiser_idPK | INT | Advertiser identifier |
| advertiser_name | VARCHAR(100) | Advertiser name |
| vertical | VARCHAR(50) | Advertiser industry |
| region | VARCHAR(30) | Primary region |