Write a query that finds email addresses duplicated after trimming surrounding spaces and ignoring case.

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.

Ada@Example.com and ada@example.com become the same normalized value and produce one row with count 2.

• Ignore null and whitespace-only values.
• Return only groups with more than one row.
• Do not delete or update data in this exercise.
• State that real email canonicalization rules are a product decision.

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.

• Null, empty, and spaces-only emails.
• Three or more case variants of the same value.
• A database collation that is already case-insensitive.
• International addresses whose normalization policy is not simple lowercase.

• Mix null, blank, and valid duplicate rows.
• Use leading and trailing spaces plus case differences together.
• Verify unique normalized values are not returned.

• Grouping raw email values and missing formatting variants.
• Treating a diagnostic query as a safe automatic merge plan.
• Including blank values as a duplicate account group.
• Assuming every email provider has the same canonicalization rules.