Write a query to delete duplicate records while keeping the newest record for each email?
Interview preparation resource from Gate Smashers.
Rank each email group by created_at descending and delete rows whose row number is greater than one. Use ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC, customer_id DESC) to mark the newest row (rn = 1) per email and remove rows with rn > 1. Test the DELETE inside a transaction and preview the ranked rows before deleting.
| customer_id | customer_name | created_at | |
|---|---|---|---|
| 301 | Nikhil Jain | nikhil@email.com | 2024-01-12 10:15 |
| 302 | Sara Ali | sara@email.com | 2024-02-03 09:10 |
| 303 | Nikhil Jain | nikhil@email.com | 2024-05-21 18:30 |
| 304 | Dev Patel | dev@email.com | 2024-06-11 12:45 |
WITH ranked AS (
SELECT
customer_id,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY created_at DESC, customer_id DESC
) AS rn
FROM Customer_Records
)
DELETE FROM Customer_Records
WHERE customer_id IN (
SELECT customer_id FROM ranked WHERE rn > 1
);Solution (one-liner)
Rank each email group by created_at descending and delete rows whose row number is greater than one. The ROW_NUMBER() window function identifies the newest row per email; then delete all rows with rn > 1.
- Core idea: Use ROW_NUMBER() PARTITION BY email ORDER BY created_at DESC (plus a tie-breaker) and delete where rn > 1.
How it works
ROW_NUMBER assigns rn = 1 to the newest row in each email partition; all older duplicates receive rn > 1. The query builds a ranked CTE and then deletes rows that are not the top-ranked record for their email.
- Partitioning: PARTITION BY email groups rows by email address.
- Ordering: ORDER BY created_at DESC picks the newest row first.
- Selection: Rows with rn > 1 are older duplicates to be deleted.
Tie-breaker & determinism
When timestamps can match, add a deterministic tie-breaker so the engine always picks the same row to keep. In the supplied query customer_id DESC is used as the tie-breaker so the row with the highest customer_id among equal timestamps is kept.
- Tie-breaker: Include an additional ORDER BY column (e.g., customer_id DESC) to ensure deterministic behavior when created_at values are identical.
Safety & testing
DELETE-with-CTE syntax and behavior can vary across databases. Always preview the ranked results and run the actual DELETE inside a transaction you can roll back if needed.
- Preview: Run the SELECT from the ranked CTE to confirm rn assignments before deleting.
- Transaction: Wrap the DELETE in a transaction so you can roll back if the deletion is not as expected.