SQL Interview Prep · Lesson

Deduplicating Rows Safely

Removing exact and near-duplicate rows while keeping one canonical record.

Deduplicating Rows Safely is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Deduplication Problem

"This table has duplicate rows. Remove them but keep one copy of each." Almost every data-engineering interview includes some flavor of this. The challenge is doing it safely: keeping exactly one canonical row and not accidentally deleting distinct records that merely look similar.

We will cover detecting duplicates, choosing which copy to keep, deduplicating in a SELECT, and physically deleting duplicates from a table.

Define Duplicate First

The first question to ask the interviewer: "What makes two rows duplicates?" Options include:

Exact duplicates: every column is identical.
Key duplicates: same business key (e.g. same email) but other columns may differ.

The technique differs for each. Never assume; clarifying the duplicate definition is the single most important step and interviewers expect you to ask.

Detecting Duplicates

To find duplicate keys, group by the columns that define a duplicate and keep groups with a count above one. This tells you which keys are affected and how many copies exist before you change anything.

Running a detection query first is a best practice worth voicing: you verify the scale of the problem before deleting.

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

Exact Duplicates: DISTINCT

If duplicates are truly identical across every column, a read-only deduplicated view is as simple as SELECT DISTINCT *. UNION (without ALL) also removes duplicate rows.

But DISTINCT only helps when you want the whole row deduplicated and do not need to choose which copy to keep. For key-based duplicates where columns differ, you need ranking.

-- Read-only dedup of exact-duplicate rows
SELECT DISTINCT customer_id, name, signup_date
FROM customers;

Key Duplicates: ROW_NUMBER

When rows share a key but differ in other columns, partition by the key and number each copy. rn = 1 marks the row you keep; rn > 1 marks the extras to discard.

The ORDER BY inside the window decides which copy is canonical. Choose it deliberately, for example keep the most recently updated row.

SELECT *,
  ROW_NUMBER() OVER (
    PARTITION BY email
    ORDER BY updated_at DESC
  ) AS rn
FROM users;

Selecting the Canonical Copy

Wrap the numbering in a CTE and keep only rn = 1. This returns one row per key, specifically the row your ORDER BY ranked first.

This SELECT form is non-destructive: it is perfect for building a clean view or feeding an INSERT ... SELECT into a deduplicated target table without touching the source.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC
    ) AS rn
  FROM users
)
SELECT user_id, email, name, updated_at
FROM ranked
WHERE rn = 1;

Choosing Order Matters

The ORDER BY inside the partition is a business decision, not a formality:

ORDER BY updated_at DESC keeps the freshest record.
ORDER BY created_at ASC keeps the original.
ORDER BY id ASC keeps the lowest surrogate key, useful as a stable arbitrary choice.

Add a unique tiebreaker so the chosen row is deterministic when the primary order column also ties.

ROW_NUMBER() OVER (
  PARTITION BY email
  ORDER BY updated_at DESC, id ASC
) AS rn

Physically Deleting Duplicates

To actually remove duplicates from the table, identify the extra rows (rn > 1) and delete them. In Postgres and SQL Server you can delete using a CTE; in MySQL a self-join or subquery is common.

Always run the matching SELECT first to preview exactly which rows will vanish. Deleting blind is how candidates fail this question.

WITH ranked AS (
  SELECT ctid,
    ROW_NUMBER() OVER (
      PARTITION BY email ORDER BY updated_at DESC, id ASC
    ) AS rn
  FROM users
)
DELETE FROM users
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

The Self-Join Delete Pattern

A classic portable approach keeps the row with the smallest id per duplicate key and deletes the rest using a self-join. It does not need window functions, which matters on older engines.

The join condition pairs each row with another row sharing the same key but a smaller id; any row that has such a smaller-id twin is a duplicate to delete.

DELETE u1
FROM users u1
JOIN users u2
  ON u1.email = u2.email
 AND u1.id > u2.id;

Safety Checklist

Before deleting, protect yourself:

Wrap the delete in a transaction so you can ROLLBACK if the count looks wrong.
Run SELECT COUNT(*) of the rows-to-delete first and sanity-check it.
Consider a backup table: CREATE TABLE users_bak AS SELECT * FROM users.
Confirm your PARTITION BY columns truly define a duplicate, or you may delete distinct records.

BEGIN;
-- run the DELETE, inspect row count
-- COMMIT; if correct, otherwise ROLLBACK;

Near-Duplicates and Normalization

Sometimes rows are not exactly equal but logically the same: 'Ann@X.com' vs 'ann@x.com', or trailing spaces. Partition on a normalized expression rather than the raw column.

Mentioning normalization shows maturity: real-world duplicates often hide behind case, whitespace, or formatting differences that a naive key comparison misses.

ROW_NUMBER() OVER (
  PARTITION BY LOWER(TRIM(email))
  ORDER BY updated_at DESC, id ASC
) AS rn

Quick Check

Pick the safe deduplication approach.

Recap: Safe Deduplication

Deduplicate methodically:

First define what a duplicate is, then detect with GROUP BY / HAVING COUNT(*) > 1.
Exact duplicates → DISTINCT. Key duplicates → ROW_NUMBER partitioned by the key, keep rn = 1.
The window ORDER BY chooses the canonical copy; add a unique tiebreaker.
Delete the rn > 1 rows inside a transaction after previewing the count.
Normalize keys to catch near-duplicates.

Frequently asked questions

Is the “Deduplicating Rows Safely” lesson free?

Yes — the full text of “Deduplicating Rows Safely” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Deduplicating Rows Safely”?

Removing exact and near-duplicate rows while keeping one canonical record. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Deduplicating Rows Safely” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep