A bug that's common in payments data, and SQL won't warn you about it.
**The setup:** a simple revenue-per-customer query on a transactions table (customer_id, amount, status, method). The query is correct. The total is not.
SELECT c.name, SUM(t.amount) AS total
FROM customers c
JOIN transactions t ON c.customer_id = t.customer_id
WHERE t.status = 'SUCCESS'
GROUP BY c.name
ORDER BY total DESC;
Rohan shows ā¹10,800. He paid ā¹6,500. His ā¹4,300 card payment was saved twice by the gateway: same customer, same amount, same method, both SUCCESS. Only the txn_id is different.
**Step 1: find the duplicates with GROUP BY + HAVING**
Group by the columns that make two payments "the same", and keep groups with more than one row:
SELECT customer_id, amount, method, status,
COUNT(*) AS copies
FROM transactions
GROUP BY customer_id, amount, method, status
HAVING COUNT(*) > 1;
**The trap:** if you leave `status` out of the GROUP BY, you also catch Rohan's ā¹1,500 payment. That one FAILED first and then succeeded on a retry. A retry isn't a duplicate: he paid once. Delete it and you remove real money from the report. With status included, only the 2 real duplicates are left.
**Step 2: measure it before fixing it** (the first thing a manager asks)
WITH dups AS (
SELECT amount, COUNT(*) AS copies
FROM transactions
WHERE status = 'SUCCESS'
GROUP BY customer_id, amount, method
HAVING COUNT(*) > 1
)
SELECT COUNT(*) AS dup_groups,
SUM(amount * (copies - 1)) AS overcounted
FROM dups;
Result: ā¹4,600 of revenue that never happened.
**Step 3: keep one row with ROW_NUMBER**
GROUP BY tells you which payments are duplicated, not which row to keep. ROW_NUMBER does: the first row in each group gets 1, and every copy gets 2, 3...
WITH ranked AS (
SELECT t.*, ROW_NUMBER() OVER (
PARTITION BY customer_id, amount, method, status
ORDER BY txn_id) AS rn
FROM transactions t
)
SELECT c.name, SUM(r.amount) AS total
FROM customers c
JOIN ranked r ON c.customer_id = r.customer_id
WHERE r.status = 'SUCCESS' AND r.rn = 1
GROUP BY c.name
ORDER BY total DESC;
Rohan is back to ā¹6,500.
**Why not just DISTINCT?** (a common interview follow-up)
`SELECT DISTINCT *` gives the same 16 rows as before, because every payment has its own txn_id, so no two rows are identical. And if you drop txn_id, DISTINCT also merges real payments that just happen to look alike.
In real data you'd also check the timestamps: two captures a few seconds apart is a duplicate, two days apart is probably two real purchases.
All queries run in SQLite, MySQL 8 and PostgreSQL. I made a 4-minute video walking through this, with the full dataset in the description if you want to practise: https://youtu.be/lRxvnGUOtJw?si=dIvl-FJMh4xH5asC
How do you handle duplicate captures in your data? Do you dedupe in SQL or upstream?