Write a query to identify duplicate rows in a Transactions table based on customer_id, amount, and date.

Answer

Use ROW_NUMBER() partitioned by the columns that define a "duplicate" - any partition with more than one row contains duplicates.

Solution

SELECT *
FROM (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id, amount, date ORDER BY transaction_id) AS rn
  FROM Transactions t
) x
WHERE rn > 1;

Step by Step

1. PARTITION BY customer_id, amount, date groups rows that share the exact same customer, amount and date together.
2. ROW_NUMBER() assigns 1, 2, 3... within each group, ordered arbitrarily by transaction_id (any tiebreaker column works).
3. Rows where rn > 1 are the "extra" copies beyond the first - the actual duplicates.

Alternative Method: GROUP BY with HAVING

SELECT customer_id, amount, date, COUNT(*) AS duplicate_count
FROM Transactions
GROUP BY customer_id, amount, date
HAVING COUNT(*) > 1;

This version returns one summary row per duplicate group with a count, rather than every individual duplicate row - useful when you only need to know which combinations repeat, not each specific row.

A Common Follow-Up: Removing the Duplicates

DELETE FROM Transactions
WHERE transaction_id IN (
  SELECT transaction_id FROM (
    SELECT transaction_id,
           ROW_NUMBER() OVER (PARTITION BY customer_id, amount, date ORDER BY transaction_id) AS rn
    FROM Transactions
  ) x WHERE rn > 1
);

Key Exam Traps

• Defining "duplicate" correctly: The interviewer's exact definition of a duplicate (which columns must match) changes the PARTITION BY list entirely - always confirm this first rather than assuming all columns must match.
• GROUP BY alone can't identify individual rows - it only tells you duplicates exist, not which specific row to keep or delete. That's exactly why ROW_NUMBER() is needed for row-level actions.
• Ordering inside the window matters when deleting - without a deterministic ORDER BY (like a timestamp or ID), which specific "duplicate" row survives is technically undefined behavior in some engines.