Database Analyst · LOWER · TRIM · GROUP BY · HAVING
2. Duplicates hiding behind case and spaces
Concept: LOWER · TRIM · GROUP BY · HAVINGshow
Hand-typed text rarely matches exactly: ' Ann@X.com' and 'ann@x.com' are the same person. Normalize first, LOWER(TRIM(col)), and group on that expression. NULLs all fall into one group, so drop them before calling anything a duplicate.
Example (different data):
SELECT UPPER(TRIM(plate)) AS plate, COUNT(*) AS copies FROM parking_permits WHERE plate IS NOT NULL GROUP BY UPPER(TRIM(plate)) HAVING COUNT(*) > 1;
Your task
The CRM is about to send a welcome series, and marketing doesn't want anyone getting it twice because their email was typed differently.
Treating emails as equal after trimming spaces and lowercasing, return email (the normalized value), copies (how many contacts share it) and first_id (the lowest id among them) for every email used by 2 or more contacts. Ignore contacts with no email. Sort by email.
Starting Postgres in your browser…
Checking your access…