Opening drill
Interview question
Find duplicate normalized email addresses
Groups normalized email values to identify duplicates before adding or repairing a uniqueness rule.
TL;DR
Groups normalized email values to identify duplicates before adding or repairing a uniqueness rule.
Normalization, GROUP BY, HAVING, null handling, cleanup safety, and constraint preparation.
Practice the problem like a real interview: restate, reason, implement, and test.
Table: Users(UserId, Email). Return NormalizedEmail and DuplicateCount for values appearing more than once after LTRIM, RTRIM, and lowercase normalization. Ignore null and blank emails.
Next in SQL Practice: Avoid Fanout Totals
Ada@Example.com and ada@example.com become the same normalized value and produce one row with count 2.
I compute the normalized value once with CROSS APPLY, filter empty results, group by that same expression, and keep duplicate groups with HAVING. This query is a diagnostic step. Before cleanup I would inspect the underlying accounts and define merge rules, then enforce the chosen normalized key with a persisted column or application-owned canonical field plus a unique index.
SELECT
normalized.NormalizedEmail,
COUNT_BIG(*) AS DuplicateCount
FROM dbo.Users AS u
CROSS APPLY
(
VALUES (LOWER(LTRIM(RTRIM(u.Email))))
) AS normalized(NormalizedEmail)
WHERE normalized.NormalizedEmail IS NOT NULL
AND normalized.NormalizedEmail <> ''
GROUP BY normalized.NormalizedEmail
HAVING COUNT_BIG(*) > 1
ORDER BY DuplicateCount DESC, normalized.NormalizedEmail;
Without a stored normalized key, the expression usually requires scanning and computing over candidate users. For recurring lookup or enforcement, store a deterministic normalized value and index it rather than repeatedly normalizing the column at query time.
Spot a weak answer, missing edge case, or clearer explanation? Send it in.