Finding Duplicates

📌 Definition:

📖 Detailed Explanation:

Duplicate data is one of the most common defects testers hunt, arising from missing unique constraints, retried operations, or faulty imports. The canonical detection query groups by the column(s) expected to be unique and filters HAVING COUNT(*) > 1, returning each duplicated value with its occurrence count. To see the actual duplicate rows (not just the values), you either join that result back to the table or use a window function (ROW_NUMBER partitioned by the key) and select rows where the row number is greater than 1 — which also underpins de-duplication, since those are the rows you'd delete to keep one copy. Composite duplicates (same first_name + last_name + dob) group by all the relevant columns. A subtlety: values differing only by case or trailing whitespace ('Test@x.com' vs 'test@x.com ') may be logical duplicates that an exact GROUP BY misses, so normalization (LOWER, TRIM) is often needed for real-world duplicate detection.

🔑 Key Points:

• Core pattern: GROUP BY the unique column(s) HAVING COUNT(*) > 1
• Returns the duplicated values and how many times each occurs
• To see/delete the actual extra rows, use ROW_NUMBER() partitioned by the key
• Composite duplicates group by all relevant columns together
• Normalize (LOWER/TRIM) to catch case/whitespace 'logical' duplicates

🌍 Real-World Example:

📎 Code Example:

-- Which emails are duplicated, and how many times?
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

-- Catch case/whitespace duplicates too
SELECT LOWER(TRIM(email)) AS normalized_email, COUNT(*) AS occurrences
FROM users
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

-- See the actual extra rows (row number > 1 = duplicates to remove)
SELECT * FROM (
  SELECT id, email,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
) t
WHERE rn > 1;