# Double Vision > Before the records move, the ones wearing the same name twice have to surface. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: UKG ## Problem Ahead of a CRM migration, the data quality team is hunting for email addresses that were entered against more than one account. For each email tied to multiple accounts, return the address, how many accounts carry it, and the earliest and most recent signup dates, with the most-repeated addresses first. ## Tables ### users - user_id INTEGER - email TEXT - signup_date TEXT - username TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ## Expected output First 4 of 4 rows: | email | occurrence_count | earliest_signup | latest_signup | | --- | --- | --- | --- | | haruki@example.com | 2 | 2025-07-07 | 2026-09-02 | | hayley_b@example.com | 2 | 2024-07-27 | 2026-02-12 | | henry@example.com | 2 | 2023-05-25 | 2024-04-24 | | hiro@example.com | 2 | 2024-12-02 | 2025-05-25 | ## Worked solution and explanation ### What this problem really is Strip the migration costume off and this is a 'collapse rows into groups, then keep only the crowded groups' question. Everyone writes `GROUP BY email`. Two things separate the clean answer from the messy one. First, the count filter has to run AFTER the rows are grouped, which means `HAVING`, not `WHERE`. Second, a pile of `NULL`-email rows is a missing-data defect, not a duplicate. Forget to exclude `NULL` and you hand the migration team a phantom duplicate that is really every account someone never gave an email: 16 accounts on this data, sitting at the top of the list. --- ### Break down the requirements #### Step 1: Group by `email` The team wants addresses tied to more than one account, so the unit of the answer is the address. `NULL` is the absence of an address, not one, so a clump of `NULL`-email rows is a missing-data problem, not a duplicate-email one. Collapse the remaining rows by address with `GROUP BY email`. #### Step 2: Filter to real duplicates Drop missing addresses up front with `WHERE email IS NOT NULL`, then keep only the crowded groups with `HAVING COUNT(*) > 1`. The count condition has to be `HAVING`: `WHERE` runs before grouping, so it cannot see a per-group count. #### Step 3: Extract the date boundaries and order Inside each surviving group, `COUNT(*)` is the occurrence count, `MIN(signup_date)` is the earliest signup and `MAX(signup_date)` is the most recent. Order by `occurrence_count DESC`, then `email ASC`, so the worst offenders surface first and ties come back in a stable order. --- ### The solution **Group, count, and filter for duplicates** ```sql SELECT email, COUNT(*) AS occurrence_count, MIN(signup_date) AS earliest_signup, MAX(signup_date) AS latest_signup FROM users WHERE email IS NOT NULL GROUP BY email HAVING COUNT(*) > 1 ORDER BY occurrence_count DESC, email ASC ``` > **One scan, one hash aggregate** > > A single sequential scan of `users` feeds the hash aggregate, and the `NULL` filter trims rows before they reach it. With an index on `email`, the planner may switch to an index-only scan. `HAVING` runs on the aggregated groups, so its cost is negligible next to the scan. On 15M rows this stays a one-pass aggregate. > **`WHERE` versus `HAVING` is answered without a pause** > > This is a warm-up, so interviewers watch for speed and precision. Hesitating over whether the count filter goes in `WHERE` or `HAVING` is a red flag at any level, and letting a `NULL`-email group through tells them you did not think about what `NULL` means. Strong candidates write this and name the `NULL` exclusion in under two minutes. > **Same mailbox, different spelling** > > Case sensitivity is the next thing a careful engineer raises. If addresses were stored mixed-case ('User@Example.com' vs 'user@example.com'), those would be the same mailbox but different strings, and a plain `GROUP BY email` would miss the match. This prompt does not require it, but offering `LOWER(email)` as the next step shows data-quality instinct. --- ## Common follow-up questions - How would you handle case-insensitive duplicate detection? _(Tests `LOWER(email)` in the grouping key and awareness of collation settings.)_ - What if you also needed to return the `user_id` values of the duplicate accounts? _(Tests `GROUP_CONCAT` / `STRING_AGG`, or a join back to `users`, to list the ids in each duplicate group.)_ - How would you deduplicate and keep only the earliest account? _(Tests `DELETE` with a subquery, or `ROW_NUMBER()` partitioned by `email` and ordered by `signup_date`.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/double_vision) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.