Task

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.

Table Definitions

lead_submissions

ad_campaigns

Sample Data

lead_submissions

ad_campaigns

Expected Output