Find Duplicates After Email Normalization
Group on LOWER(TRIM(email)) and return repeated normalized emails.
- Aggregation
- HAVING
- String functions
- Filtering
- Sorting
Exercise brief
Understand the request
Identity resolution engineer Case and edge whitespace differences are hiding collisions in customer email identities.
Case and edge whitespace differences are hiding collisions in customer email identities. Group on LOWER(TRIM(email)) and return repeated normalized emails.
Return
- Return normalized_email and duplicate_count.
- Order by duplicate_count descending, then normalized_email.
Constraints
- Exclude NULL and whitespace-only emails before grouping.
- Apply the same canonical expression to every row in a group.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
quality_records
emailTEXT
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Canonicalize before grouping, not only in the output.
Hint 2
Filter NULL and trimmed-empty values before the aggregate.
Hint 3
Group by LOWER(TRIM(email)) and keep groups with more than one row.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT LOWER(TRIM(email)) AS normalized_email, COUNT(*) AS duplicate_count FROM quality_records WHERE email IS NOT NULL AND TRIM(email) <> '' GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1 ORDER BY duplicate_count DESC, normalized_email;Why this works
Duplicate detection is only as good as its canonical key. This query makes case and edge-space rules explicit and keeps missing identities out of collision counts.
Success check
The padded and mixed-case Alice values collapse into one three-row group while blank values are excluded.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| normalized_email | duplicate_count |
|---|---|
| alice@example.com | 3 |
| bob@example.com | 2 |
| dave@example.com | 2 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
CASE Statements & Conditional Logic
Build NULL-aware classifications, precedence-safe decisions, flags, scores, and guarded calculations with portable CASE expressions.
Open the interactive workspace and practice across SQL topics.