Nimbus CRM stores inbound sales leads, but duplicate rows can appear because the source system does not provide a reliable unique business key. You need to identify duplicate lead records and determine which rows should be removed.
Write a SQL query to return the duplicate rows that should be deleted, keeping only the earliest created_at row within each duplicate group.
full_name, email, phone, and source_channel are all the same.created_at row.created_at.customer_leads
| column | type | description |
|---|---|---|
| lead_row_id | INT | Physical row identifier used only for cleanup output |
| full_name | VARCHAR(100) | Lead full name |
| VARCHAR(150) | Lead email address | |
| phone | VARCHAR(30) | Lead phone number |
| source_channel | VARCHAR(50) | Acquisition source |
| created_at | TIMESTAMP | Time the row was inserted |
| lead_row_id | full_name | phone | source_channel | created_at | |
|---|---|---|---|---|---|
| 108 | Maya Chen | maya@example.com | 555-0101 | web | 2024-02-03 09:00:00 |
| 101 | Liam Patel | liam@example.com | 555-0100 | web | 2024-02-01 09:15:00 |
| 110 | Noah Kim | 555-0103 | referral | 2024-02-04 08:30:00 | |
| 103 | Liam Patel | liam@example.com | 555-0100 | web | 2024-02-01 09:18:00 |
| 105 | Ava Singh | ava@example.com | NULL | partner | 2024-02-02 11:00:00 |
| 102 | Emma Ross | emma@example.com | 555-0102 | ad | 2024-02-01 10:00:00 |
| 111 | Noah Kim | 555-0103 | referral | 2024-02-04 08:45:00 | |
| 104 | Liam Patel | liam@example.com | 555-0100 | web | 2024-02-01 09:20:00 |
| lead_row_id | full_name | phone | source_channel | created_at | duplicate_rank | |
|---|---|---|---|---|---|---|
| 103 | Liam Patel | liam@example.com | 555-0100 | web | 2024-02-01 09:18:00 | 2 |
| 104 | Liam Patel | liam@example.com | 555-0100 | web | 2024-02-01 09:20:00 | 3 |
| 106 | Ava Singh | ava@example.com | NULL | partner | 2024-02-02 11:05:00 | 2 |
| 109 | Maya Chen | maya@example.com | 555-0101 | web | 2024-02-03 09:05:00 | 2 |
| 111 | Noah Kim | 555-0103 | referral | 2024-02-04 08:45:00 | 2 |