Find duplicate email addresses
Return each non-NULL email used by at least two users, with its occurrence count. Return only the requested columns and follow the specified ordering. This question probes filter nulls before grouping, then use having to retain repeated business keys.
Input
Setup script defining users(id INT, email VARCHAR(80)).
Output
email, occurrences; ordered by email.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE users(id INT, email VARCHAR(80)); INSERT INTO users VALUES (1,'a@x'),(2,'a@x'),(3,'b@x'),(4,NULL);
Expected result
email | occurrences a@x | 2
Another dataset (1)
CREATE TABLE users(id INT, email VARCHAR(80)); INSERT INTO users VALUES (1,NULL),(2,NULL),(3,'b@x'),(4,'b@x');
Expected result
email | occurrences b@x | 2
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Group by email, not by ID.
- Apply COUNT(*) > 1 after aggregation.
Target complexity: time O(n) · space O(n)