How do you delete duplicate rows in place using a CTE over ROW_NUMBER()?

## From selecting survivors to removing losers The read-only deduplication pattern keeps `rn = 1`. Cleaning the table up is the mirror image: you compute the same numbering and delete everything with `rn > 1`. The inversion is where most candidates slip — under time pressure people copy the `SELECT` and leave `rn = 1` in the `DELETE`, which removes precisely the rows they intended to preserve. In a destructive statement that mistake is unrecoverable outside a transaction or a backup. ## The statement, step by step ```sql WITH ranked AS ( SELECT customer_id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, customer_id DESC) AS rn FROM customers ) DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM ranked WHERE rn > 1); ``` 1. **`PARTITION BY email`** declares what "duplicate" means. A composite rule partitions by several columns, and a normalising rule partitions by an expression such as `LOWER(email)`. 2. **`ORDER BY created_at DESC, customer_id DESC`** decides the survivor. The trailing `customer_id` makes the ordering total, so two rows with identical timestamps still have a defined winner and re-running the statement is repeatable. 3. **`rn > 1`** selects every row of a group except its first. Keys that occur once produce only `rn = 1` and are never touched. 4. **The outer `DELETE` targets rows by primary key.** This is what actually connects the ranked set back to physical rows. ## Why the delete must go through a unique identifier A `DELETE` removes every row matching its `WHERE`. If you tried `DELETE FROM customers WHERE email IN (SELECT email FROM ranked WHERE rn > 1)` you would delete the survivors too, because they share the email. The predicate must therefore identify individual rows, which is what the primary key gives you. A table with no unique column cannot be deduplicated this way at all and needs a rebuild-and-swap instead. ## Two statement shapes, and portability Some engines let the CTE itself be the delete target — `WITH ranked AS (...) DELETE FROM ranked WHERE rn > 1` — treating the CTE as an updatable view over the base table. That is compact but not universally available. Other engines reject a subquery that reads the same table the `DELETE` is modifying. The safest habit is: - prefer the **delete-by-primary-key** form above, and - if the engine refuses to read the target table in a subquery, materialise the loser ids first: ```sql CREATE TEMPORARY TABLE losers AS SELECT customer_id FROM (SELECT customer_id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, customer_id DESC) AS rn FROM customers) r WHERE rn > 1; DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM losers); ``` Check your engine's documentation for which forms it accepts rather than assuming; this is one of the areas where dialects genuinely diverge. ## Safety practice an interviewer wants to hear - **Preview first.** Run the CTE with `SELECT * FROM ranked WHERE rn > 1` and compare its count against `SELECT COUNT(*) - COUNT(DISTINCT email) FROM customers`, which is how many rows a correct dedup should remove when the key is a single column. - **Wrap it in a transaction** so a wrong predicate can be rolled back. - **Deterministic ordering** so the run is reproducible and a re-run after a partial failure keeps the same survivors. - **Children first.** Rows being deleted may be referenced by other tables; decide whether those references should be repointed at the survivor before the delete, rather than discovering it through a constraint violation. - **Close the hole.** After the cleanup, add a `UNIQUE` constraint on the deduplication key so the duplicates cannot come back; otherwise you will run this statement again next quarter. ## What good and bad answers look like A weak answer deletes with `rn = 1`, ranks without a tiebreaker, matches on the duplicate key instead of the primary key, or reaches for a row-by-row loop in application code. A strong answer writes the ranked CTE, deletes `rn > 1` by primary key, states why the ordering must be total, previews the count, and finishes with the constraint that prevents a repeat.