Write a query to delete duplicate rows keeping one in SQL Server.

Short answer: Use a CTE with ROW_NUMBER() PARTITION BY the duplicate key, then DELETE WHERE rn > 1. This is the standard T-SQL interview answer.

Sample solution

WITH d AS (
    SELECT EmpId,
           ROW_NUMBER() OVER (PARTITION BY Email ORDER BY EmpId) AS rn
    FROM Employees
)
DELETE FROM d WHERE rn > 1;

Edge cases to mention

• Which row to keep (MIN EmpId vs latest UpdatedAt)
• Composite duplicate keys

Mistakes to avoid

• DELETE without ROW_NUMBER preview SELECT first
• Using DISTINCT in DELETE incorrectly

Always SELECT from the CTE before DELETE in real work — mention that safety habit.