Select rows to keep when removing duplicates
Users may share the same email, including NULL. Return the complete row with the smallest ID per email; treat all NULL emails as a single group. Do not execute DELETE. Return only the requested columns and follow the specified ordering. This question probes row_number chooses a deterministic survivor even for the null partition; select previews deletion safely.
Input
Setup script defining users(id INT, email VARCHAR(80)).
Output
id, email; ascending retained id.
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 (4,'a@x'),(1,'a@x'),(3,NULL),(2,NULL),(5,'b@x');
Expected result
id | email 1 | a@x 2 | NULL 5 | b@x
Another dataset (1)
CREATE TABLE users(id INT, email VARCHAR(80)); INSERT INTO users VALUES (1,NULL),(2,NULL);
Expected result
id | email 1 | NULL
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Partition by the duplicate business key.
- Order each partition by ID and keep row number one.
Target complexity: time O(n log n) · space O(n)