Duplicate Customer Records
Problem
A client's CRM export has a customers(id, full_name, email, phone, created_at) table where the same person appears several times with small differences: different letter case in the email, spaces or a leading +2 in the phone, and later created_at values for the duplicates. Write a query that identifies duplicate groups by normalised email or normalised phone, keeps the earliest record per group as the survivor, and lists every other record with the id of its survivor. Then tell me how you would actually run the merge safely.
Examples
Example 1
Rows: (1, Ahmed Ali, Ahmed@x.com, 01012345678, Jan 1), (2, ahmed ali, ahmed@x.com, +201012345678, Feb 3).
Output: duplicate_id=2, survivor_id=1 — same email after lowercasing, same phone after stripping +2.
Example 2
Rows: (7, Sara, sara@x.com, 0111, Mar 1), (8, Sara, s.ara@x.com, 0111, Mar 2), (9, Sara, sara@x.com, 0222, Mar 3).
Output: 8 → 7 (same phone) and 9 → 7 (same email). Note that 8 and 9 share nothing directly but both point to 7.
Constraints
- Standard SQL; window functions allowed
- Normalisation: lowercase and trim email; strip spaces and a leading
+2from phone - Do not delete anything in the query — this is a report, the merge is a separate step
What they look for
Normalising inside a CTE, ROW_NUMBER() OVER (PARTITION BY key ORDER BY created_at, id), handling the two-key case without double-counting, and a sensible answer on running the merge (transaction, backup table, foreign keys re-pointed first).