Detailed explanation
Finding and deleting duplicates starts with defining what makes two rows duplicates. In a users table, this might mean the same email, or the same combination of email and tenant_id, rather than identical values in every column. A primary key such as id normally differs between rows, so including it in the duplicate definition would hide repeated business data. Also clarify which row must survive: the smallest id, the latest updated_at, or the most complete record. This rule determines whether cleanup preserves the correct information.
To find duplicate email groups, use SELECT email, COUNT(*) AS occurrences FROM users GROUP BY email HAVING COUNT(*) > 1;. GROUP BY forms one group per email, and HAVING filters groups after aggregation. This query reports duplicate keys and their counts, not all underlying rows. To inspect the individual records, use a window count or join the grouped results back to users, accounting for NULL values if necessary. For a composite definition, include every relevant column in GROUP BY.
To delete extra copies while keeping one, assign ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) to each row. PARTITION BY separates duplicate groups, while ORDER BY makes the lowest id receive row number 1. Assuming id is unique and non-null, a common pattern is: WITH d AS (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users) DELETE FROM users WHERE id IN (SELECT id FROM d WHERE rn > 1);. The CTE supplies ranked identifiers, and DELETE removes only identifiers ranked above 1. For latest-record retention, use ORDER BY updated_at DESC, id DESC, with an explicit policy for null timestamps.
Before deleting, preview the same ranked rows with SELECT and verify the survivor rule. Use a transaction where supported, consider foreign-key dependencies, and account for concurrent writes; otherwise, duplicates may reappear during cleanup. SQL syntax and restrictions on deleting from a table referenced by a subquery vary by database, so adapt the pattern to the target engine. After cleanup, enforce the business key with an appropriate UNIQUE constraint or unique index. Interviewers assess whether you can define duplicates correctly, retain a deterministic survivor, and make the operation safe rather than merely produce a DELETE statement.
Key points to remember
- Define duplicates using business-key columns, excluding a unique row identifier.
- Use GROUP BY with HAVING COUNT(*) > 1 to find repeated keys.
- Use ROW_NUMBER() partitioned by the duplicate columns to identify surplus rows.
- Choose a deterministic survivor order and delete through a unique row identifier.
- Preview deletions, handle concurrency, and prevent recurrence with a suitable uniqueness rule.
Common mistakes candidates make
- Deleting every row whose email appears in a duplicate group, thereby removing the intended survivor too.
- Using a non-unique ordering without a tie-breaker when the survivor must be predictable.
- Ignoring NULL handling, case sensitivity, or whitespace when defining duplicate values.
Likely follow-up questions
Why use ROW_NUMBER() instead of RANK()?
ROW_NUMBER() assigns a distinct number to every row within a partition, so exactly one receives 1. RANK() can assign 1 to multiple tied rows, leaving multiple survivors when you delete ranks above 1.
What if the table has no unique row identifier?
Deleting by duplicated values alone cannot distinguish the retained copy from the extras. Depending on the database, add a surrogate key, use a supported physical row locator carefully, or rebuild the data through a staging table while preserving schema constraints and dependencies.
How do NULL values affect duplicate handling?
GROUP BY and PARTITION BY place rows with the same grouping values, including NULL, into the same group or partition. UNIQUE constraints handle NULL differently across databases, so confirm whether the prevention rule matches your cleanup definition.
Interview tip: Explain the duplicate key first, show detection and ranking, then finish with the survivor rule and safety checks.