Duplicate Detection

Finding duplicates in SQL is like sorting mail at a busy front desk: you do not just want to know that two envelopes look alike, you want to know which one arrived first and which ones are extras.

Question: Duplicate Detection

Answer: In SQL, duplicate detection usually means finding rows that match on a business key, such as email, order id, or a combination of columns. The most common solution is a window function like ROW_NUMBER() with PARTITION BY to group matching rows and ORDER BY to choose a stable winner. Rows with row_number > 1 are the duplicates, and row number 1 is the row you keep.

Interview-Ready Answer: I would detect duplicates by partitioning rows on the business key and using ROW_NUMBER() to number rows inside each group. Then I would treat rn = 1 as the keeper and rn > 1 as duplicates. I like this approach because it is deterministic when I add a stable ORDER BY, such as created time plus a unique id, so I can safely report, delete, or dedupe the extras.

Detailed Explanation: Duplicate detection is not the same thing as removing duplicate output rows. A window function is a calculation that looks at a set of related rows while still returning one result row per input row. That is why it is so useful here: you can label every row as first, second, third, and so on, instead of collapsing the group too early.

1) Start with the business key

The first step is deciding what makes two rows the same. In a users table, the key might be email. In an orders table, it might be order_number. Sometimes it is a composite key, which means several columns together, like customer_id plus order_date plus amount.

2) Partition the data

PARTITION BY tells SQL, 'put matching rows into the same bucket.' Every bucket is processed separately. If you partition by email, every row with the same email value gets the same counter sequence. If you partition by multiple columns, then all of those values must match for rows to be considered duplicates.

3) Order rows inside each bucket

ROW_NUMBER() needs an ORDER BY so it can decide which row is first, second, third, and so on. This is the part many candidates miss. Without a stable order, the database may pick a different row as the keeper on different runs, which is dangerous if you later delete rows based on that numbering. A good tie-breaker is usually a unique column like id or a load timestamp plus id.

4) Assign row numbers

Once rows are partitioned and ordered, SQL walks through each bucket and numbers the rows starting at 1. The first row is the one you usually keep. Every later row is a duplicate candidate. If you also want to know how big the duplicate group is, add COUNT(*) OVER (PARTITION BY ...) alongside ROW_NUMBER().

5) Filter the results

To find duplicates, filter for rn > 1. To keep one copy, keep rn = 1. To delete duplicates, use a CTE or subquery that ranks rows first, then delete from the ranked set. The important habit is: rank first, filter second.

6) Compare the main options

DISTINCT is useful when you only care about the final result set. GROUP BY is useful when you want summaries like counts. But duplicate detection is usually a row-level problem: you want to know exactly which physical rows are extras. That is where ROW_NUMBER() wins.

Performance and complexity

Window functions are not free. In many engines, ROW_NUMBER() requires the data to be sorted by the partition and order keys. The expensive part is the sort, so the overall cost is often around O(n log n) time, not pure O(n). Space usage depends on how much data must be buffered for the sort; if memory is too small, the database may spill to temporary disk files. On a table with millions of rows, that spill can be the difference between a fast query and a query that runs for minutes.

If there is an index that matches the partition and ordering columns, the engine may avoid some sorting work. For example, an index on (email, created_at, id) can help a query that partitions by email and orders by created_at, id. If you normalize the key with LOWER(email), some databases can only use that index if you create a matching functional index. That is a classic production gotcha.

Important edge cases

• NULLs: In most databases, rows with NULL in the partition key end up in the same window partition. If NULL means 'unknown' and should not count as a duplicate, filter those rows out first.
• Case sensitivity: Alice@example.com and alice@example.com may or may not be the same depending on business rules and collation. Normalize with LOWER() if needed.
• Ties: If two rows have the same timestamp, add a unique tie-breaker like id. Otherwise the 'first' row can be arbitrary.
• Composite duplicates: Use multiple columns in PARTITION BY when one column alone is not enough to define sameness.
• Exact duplicates: If every column is identical, you can still rank them, but you must decide which copy to keep using a surrogate key or load order.

Bottom line: the mental model is 'bucket the matching rows, number them, keep the first one.' That is the cleanest and safest duplicate-detection pattern in SQL.

Real-World Story: Imagine a checkout service in an e-commerce system. Every order gets an idempotency_key so retries do not create two charges. A developer notices duplicate rows in the orders table and uses DISTINCT in a report, thinking the problem is solved. The report looks clean, but the underlying duplicate inserts are still there, so shipping and billing systems both process the same purchase twice.

In production, the symptom is noisy and painful: support tickets about double charges, order history showing two nearly identical rows, and logs with the same idempotency key appearing more than once. Once the team switches to a window-function query, they can prove which row arrived first, flag the extra inserts, and safely dedupe while preserving the original order record. The bug was not just 'duplicates exist' — it was 'we did not know which row was the real one.' That is exactly why ROW_NUMBER() matters.

Follow-up & Tricky Questions:

• How would you return only the duplicate rows? Add a filter like WHERE rn > 1 on the ranked result. That keeps the first row out of the result and shows only the extras.
• How would you delete duplicates but keep one row? Rank first in a CTE or subquery, then delete rows where rn > 1. The key is to make the keeper deterministic before you delete anything.
• What if duplicates are based on multiple columns? Put all of those columns in PARTITION BY. For example, duplicates could be defined by customer_id, order_date, and amount together.
• How do you handle case-insensitive duplicates? Normalize the key with LOWER() or use a case-insensitive collation if your database supports it. Without normalization, Bob@example.com and bob@example.com may be treated as different rows.
• How do you know which row to keep when timestamps are equal? Add a unique tie-breaker such as a surrogate id. Otherwise the winner can change between runs, which is risky for deletes.
• Does DISTINCT solve duplicate detection? No. DISTINCT removes duplicate output rows after projection, but it does not identify which source row is the extra one. For row-level cleanup, ROW_NUMBER() is the better tool.
• Are NULL values automatically duplicates? Not by business rule, but window partitioning usually places NULL values in the same partition. If NULL means unknown and should not count, exclude those rows before ranking.
• Can I use ROW_NUMBER() without ORDER BY? For duplicate detection, no. You need a stable order to choose a keeper; otherwise the 'first' row is arbitrary and may vary.

Common Mistakes:

• Using DISTINCT too early. DISTINCT is great for cleanup in a final result, but it hides row identity. The fix is to rank rows first, then filter.
• Forgetting a deterministic ORDER BY. If you do not add a stable tie-breaker, the chosen keeper can change. The fix is to order by something unique, such as created_at, id.
• Ignoring business rules for text and NULL. Email duplicates may be case-insensitive, and NULL may mean 'unknown' instead of 'duplicate'. The fix is to normalize or filter based on the rule.
• Deleting before testing. Never run a duplicate-delete statement first. The fix is to select the ranked rows, verify the keeper, then perform the delete.

Memory Hook: 'Same bucket, ticket numbers.' Put matching rows in the same bucket, hand out 1, 2, 3, and keep ticket 1.

Cheat Sheet:

• Use PARTITION BY for the duplicate key.
• Use ROW_NUMBER() to label rows inside each group.
• Use a stable ORDER BY to choose the keeper.
• rn = 1 usually means keep; rn > 1 means duplicate.
• COUNT(*) OVER tells you how large each duplicate group is.
• Normalize case and handle NULL according to business rules.

Practice Tasks:

• Find duplicate customers by email and mark which row you would keep.
• Detect duplicate orders using customer_id plus order_date plus amount.
• Write a safe delete query that removes extras but keeps the earliest row by created_at.