How would you use GROUP BY and HAVING to find duplicate email addresses in a users table?

## The shape of the idiom "Find the duplicates" is the single most common practical use of `HAVING`, and it is a two-step thought: define what "the same" means, then keep the groups that occur more than once. ```sql SELECT email, COUNT(*) AS occurrences FROM users GROUP BY email HAVING COUNT(*) > 1 ORDER BY occurrences DESC, email; ``` `GROUP BY email` collapses the table into one group per distinct email value. `COUNT(*)` counts the rows in each group. `HAVING COUNT(*) > 1` discards every group of size one, leaving exactly the values that appear more than once, together with how often. ## Why WHERE cannot do this `WHERE` is evaluated per row, before `GROUP BY` runs. When a given row is being tested, the engine has not counted anything; there is no group and therefore no `COUNT(*)` to compare against. `WHERE COUNT(*) > 1` is rejected outright by a conforming engine. Duplicate detection is inherently a statement *about a group of rows*, so it belongs to the clause that filters groups. ## The group key is the definition of "duplicate" Whatever you list in `GROUP BY` is your definition of sameness, and this is where the real interview follow-up lives. ```sql -- duplicates within a tenant, ignoring letter case SELECT tenant_id, LOWER(email) AS email_key, COUNT(*) AS occurrences FROM users GROUP BY tenant_id, LOWER(email) HAVING COUNT(*) > 1; ``` Grouping by two columns means a pair counts as duplicated only when both parts match. Grouping by an expression such as `LOWER(email)` or `TRIM(email)` makes the comparison case- or whitespace-insensitive, which matters when the duplicates you are hunting were created by inconsistent input rather than by exact repetition. ## Counting distinct actors instead of rows Sometimes the interesting condition is not "more than one row" but "more than one distinct related value": ```sql -- emails claimed by more than one distinct account SELECT email, COUNT(DISTINCT account_id) AS accounts FROM users GROUP BY email HAVING COUNT(DISTINCT account_id) > 1; ``` The pattern is unchanged — group, aggregate, test the aggregate in `HAVING` — only the aggregate expression differs. ## What the query does and does not give you The result contains duplicated **values**, one row per offending group, not the duplicate rows themselves. If you need the actual rows — to inspect or delete them — you have to get back to row level, for example by joining the grouped result back to the base table: ```sql SELECT u.* FROM users u JOIN ( SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1 ) d ON d.email = u.email ORDER BY u.email, u.id; ``` Mistaking one for the other is a common slip: a candidate says "this gives me the duplicate rows" when it gives one summary row per duplicated value. ## Threshold variations The predicate is ordinary boolean logic over aggregates, so it generalises freely: `HAVING COUNT(*) >= 3` for values seen at least three times, `HAVING COUNT(*) = 1` for values seen exactly once (the singletons), and combinations such as `HAVING COUNT(*) > 1 AND MIN(created_at) >= DATE '2026-01-01'` to restrict attention to duplicates that all appeared recently. Note that `HAVING COUNT(*) = 0` is a dead end — a group exists only because rows produced it, so no returned group can have zero rows. ## A NULL caveat Grouping puts all NULL values of the key into a single group, so a table with many NULL emails will report NULL as a heavily "duplicated" value even though SQL's comparison rules would never call two NULLs equal. If NULL is not a duplicate in your data's terms, exclude it at row level with `WHERE email IS NOT NULL` — precisely the kind of per-row condition that belongs in `WHERE`, not in `HAVING`. ## What a strong answer sounds like Write the query without hesitating, name the group key as the definition of duplication, say why `WHERE` cannot express the predicate, and volunteer one refinement — case-insensitive grouping, a composite key, or the join-back needed to see the actual rows.