Duplicate Data

Duplicates are rows that represent the same real-world entity or event more than once.

Concept

Duplicates are rows that represent the same real-world entity or event more than once. They may be exact (all columns equal) or fuzzy (same entity with differences in case, spacing, formatting, or IDs).

Why It Matters

Duplicates inflate counts and revenue, send customers two emails, break joins (fan-out), and cause unique-constraint failures during migrations. Finding and removing them safely — and preventing them — is a routine data-quality task.

Syntax

-- Find
SELECT key_cols, COUNT(*) FROM t GROUP BY key_cols HAVING COUNT(*) > 1;

-- Rank within duplicates
ROW_NUMBER() OVER (PARTITION BY key_cols ORDER BY keep_preference) AS rn   -- keep rn = 1

Example

Sample table with duplicates:

39.1 Finding duplicates Exact duplicates on email:

Row 2 ('[email protected] ') was not detected because of case and a trailing space. Normalize before comparing:

Show full duplicate rows (not just the keys):

Or with a window count:

39.2 The ROW_NUMBER technique (choose which row to keep) Keep the most recent import per normalized email:

Rows with rn > 1 are the duplicates to remove: import_ids 1, 2, 3.

39.3 Removing duplicates safely Step 1 — back up the rows you will delete:

Step 2 — delete inside a transaction and verify the count:

(MySQL does not allow the target table in a direct subquery of DELETE; the extra derived-table layer SELECT ... FROM (...) d works around error 1093.)

SQL Server shortcut — delete through a CTE:

Tables without a primary key (fully identical rows) — rebuild instead of deleting:

Merging instead of deleting: when duplicates are referenced by other tables (orders pointing at both customer IDs), first re-point foreign keys to the surviving ID, then delete:

39.4 DISTINCT for de-duplicated output (without deleting) DISTINCT hides duplicates in a result; it does not fix the data, and using it to mask a bad join is an anti-pattern.

39.5 Preventing duplicates Also: normalize input (trim, lowercase emails, canonical phone format) at write time, use idempotency keys for API-created records (payments!), and add unique constraints on event tables such as (vehicle_id, dtc_code, detected_at).

CREATE TABLE customer_imports (
    import_id   INT PRIMARY KEY,
    full_name   VARCHAR(100),
    email       VARCHAR(120),
    imported_at DATE
);

INSERT INTO customer_imports VALUES
(1, 'Ravi Kumar', '[email protected]',   DATE '2026-01-01'),
(2, 'Ravi Kumar', '[email protected] ',  DATE '2026-02-01'),
(3, 'Sita Rao',   '[email protected]',   DATE '2026-01-15'),
(4, 'Ravi K',     '[email protected]',   DATE '2026-03-01'),
(5, 'Sita Rao',   '[email protected]',   DATE '2026-01-20'),
(6, 'John Doe',   '[email protected]',   DATE '2026-02-10');

SELECT email, COUNT(*) AS n
FROM customer_imports
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*) AS n,
       STRING_AGG(CAST(import_id AS VARCHAR(10)), ',' ORDER BY import_id) AS ids
FROM customer_imports
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1
ORDER BY email_norm;

SELECT *
FROM customer_imports ci
WHERE LOWER(TRIM(email)) IN (
    SELECT LOWER(TRIM(email)) FROM customer_imports GROUP BY LOWER(TRIM(email)) HAVING COUNT(*) > 1
)
ORDER BY LOWER(TRIM(email)), imported_at;

SELECT * FROM (
    SELECT ci.*, COUNT(*) OVER (PARTITION BY LOWER(TRIM(email))) AS dup_count
    FROM customer_imports ci
) t
WHERE dup_count > 1;

SELECT import_id, full_name, email, imported_at,
       ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email))
                          ORDER BY imported_at DESC, import_id DESC) AS rn
FROM customer_imports
ORDER BY LOWER(TRIM(email)), rn;

CREATE TABLE customer_imports_removed AS
SELECT ci.*
FROM customer_imports ci
JOIN (
    SELECT import_id,
           ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY imported_at DESC, import_id DESC) AS rn
    FROM customer_imports
) r ON r.import_id = ci.import_id
WHERE r.rn > 1;

BEGIN;

-- Portable form (PostgreSQL, SQL Server, Oracle, SQLite, MySQL 8 with derived-table wrapper)
DELETE FROM customer_imports
WHERE import_id IN (
    SELECT import_id FROM (
        SELECT import_id,
               ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email))
                                  ORDER BY imported_at DESC, import_id DESC) AS rn
        FROM customer_imports
    ) d
    WHERE d.rn > 1
);
-- Expect: 3 rows deleted

SELECT COUNT(*) FROM customer_imports;   -- 3
COMMIT;   -- or ROLLBACK if the count is not what you expected

WITH d AS (
    SELECT ROW_NUMBER() OVER (PARTITION BY LOWER(LTRIM(RTRIM(email)))
                              ORDER BY imported_at DESC, import_id DESC) AS rn
    FROM customer_imports
)
DELETE FROM d WHERE rn > 1;

CREATE TABLE t_clean AS SELECT DISTINCT * FROM t_with_dups;
-- then swap tables (rename) in a maintenance window; PostgreSQL can also use the system column ctid.

Expected Result

After de-duplication, SELECT import_id, email FROM customer_imports ORDER BY import_id;:

Common Mistakes

Deleting duplicates without a backup or transaction.

Non-deterministic ROW_NUMBER ordering (keeps a random row).

Ignoring case/whitespace differences.

Deleting "duplicates" that are referenced by other tables.

Using DELETE ... WHERE id NOT IN (SELECT MIN(id) ... GROUP BY ...) on huge tables without checking the plan.

Relying on DISTINCT in reports instead of fixing the source.

Best Practices

Decide and document the survivor rule (latest, most complete, lowest ID).

Back up, delete in a transaction, verify counts, then commit.

Enforce uniqueness in the database after cleanup.

Normalize data on entry.

Interview Relevance

Very high: "Find duplicate emails", "Delete duplicates keeping one", "Keep the latest record", "How do you prevent duplicates?".

Practice Questions

Find orders with the same customer and date (possible double submissions).

Delete duplicate rows in test_result for the same vehicle, software version, test case, and timestamp, keeping the lowest test_id.

Write a unique index that prevents duplicate DTC events for the same vehicle, code, and detection time.

Explain why SELECT DISTINCT is not a data-quality fix.