Drill 2 of 10 in Database Analyst
0

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…