Task

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.

Table Definitions

lead_submissions

campaigns

advertisers

Sample Data

Representative rows are shown in the provided dataset.

Expected Output