Allowed R6 · Within policy
{
"slug": "duplicate-customer-records",
"title": "Duplicate Customer Records",
"difficulty": "medium",
"tags": [
"sql",
"data",
"technical",
"data-eng"
],
"is_free": true,
"locked": false,
"frequency": 0.3,
"last_reported_at": "2026-07-03T13:54:53.578486Z",
"url": "https://www.mokabla.com/en/c/pwc/duplicate-customer-records",
"agent": {
"page": "https://www.mokabla.com/agent/en/c/pwc/duplicate-customer-records",
"question": "https://www.mokabla.com/agent/_call/get_question?company=pwc&question=duplicate-customer-records"
},
"company": {
"slug": "pwc",
"name_en": "PwC (Egypt / ETIC)"
},
"body_md": "## Problem\nA 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.\n\n## Examples\n**Example 1**\nRows: (1, `Ahmed Ali`, `Ahmed@x.com`, `01012345678`, Jan 1), (2, `ahmed ali`, `ahmed@x.com`, `+201012345678`, Feb 3).\nOutput: `duplicate_id=2, survivor_id=1`, same email after lowercasing, same phone after stripping `+2`.\n\n**Example 2**\nRows: (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).\nOutput: 8 → 7 (same phone) and 9 → 7 (same email). Note that 8 and 9 share nothing directly but both point to 7.\n\n## Constraints\n- Standard SQL; window functions allowed\n- Normalisation: lowercase and trim email; strip spaces and a leading `+2` from phone\n- Do not delete anything in the query, this is a report, the merge is a separate step\n\n## What they look for\nNormalising 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).",
"follow_ups_md": "- Example 2 shows a chain. How would you handle transitive duplicates in general?\n- How do you avoid merging two different people who share a family phone?\n- What would you log so the merge can be undone?",
"access": "Question bodies are paid content: a person buys 30 days of access per company (200 EGP or 200 points) in the human app. Agents get titles, tags and difficulty; bodies only for free questions."
}