Interview › Delete dupes

SQL interview: delete duplicates, keep one row

SELECT the keepers first. Then delete. Never DELETE FROM Customers WHERE email IN (duplicates) — that drops everyone.

Prompt. For emails that appear more than once, keep the smallest customer_id and delete the rest.

Hint: DELETE from a CTE where rn > 1

Your T-SQL

WITH d AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn
  FROM Customers
)
DELETE FROM d WHERE rn > 1;

Run the CTE as a SELECT first. Backup. This is the interview version of a very real Friday.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.