How would you write a query to identify duplicate records in a customer database for Pattern’s ecommerce partner data?
Treat records as duplicates when they belong to the same partner and have the same email after trimming whitespace and ignoring letter case. Exclude NULL and empty email values.
partner_id, partner_name, normalized_email, duplicate_count, and comma-separated record_ids.partner_id, then normalized_email.| Column | Type | Description |
|---|---|---|
| partner_idPK | INT | Unique ecommerce partner identifier |
| partner_name | VARCHAR(120) | Partner display name |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer record identifier |
| partner_id | INT | Ecommerce partner associated with the record |
| VARCHAR(255) | Customer email address as received from the partner | |
| full_name | VARCHAR(150) | Customer name as received from the partner |