task TRIM / LOWER / SUBSTR / REPLACE · medium

Normalize contact import collisions

Find duplicate valid emails after lowercasing and trimming outer ASCII spaces, tabs, carriage returns and line feeds. Return normalized_email, source_rows and the smallest source id as first_id.

what to return

Columns

normalized_emailsource_rowsfirst_id in this order

Rows

5 rows · in the order asked

Watch for

For this exercise only: exactly one @; a nonempty local part; domain has a dot that is neither its first nor last character; no consecutive domain dots; no ASCII space, tab, CR or LF anywhere after trimming.

This is a deliberately limited import rule, not full email-standard validation. Keep plus aliases distinct; do not remove dots or plus suffixes.

Return only groups with more than one valid source row, ordered by normalized_email.

Skill

Find normalized duplicate contacts while excluding malformed identifiers. Refresher: CAST, text types & NULL arithmetic →

expected result

Your query should return exactly these columns and values. Run it and compare.

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.