← All lessons
Querying data · Lesson 6 of 7

Finding and removing duplicates

Duplicates come from retries, double clicks, a file loaded twice or a join that fans out. The first step isn't SQL: decide what makes two rows the same. That's the key. In signups the key is the email address, and email addresses don't care about capital letters.

To find duplicates, group by the key and keep the groups with more than one row:

SELECT email, COUNT(*) AS n
FROM signups
GROUP BY email
HAVING COUNT(*) > 1;
Returns
email           | n
----------------+--
ben@example.com | 3
(1 row)

That missed one, because ana@example.com and ANA@example.com are different text. Group on a cleaned-up key instead: LOWER() (and TRIM() for stray spaces).

SELECT LOWER(email) AS email, COUNT(*) AS n
FROM signups
GROUP BY LOWER(email)
HAVING COUNT(*) > 1
ORDER BY email;
Returns
email           | n
----------------+--
ana@example.com | 2
ben@example.com | 3
(2 rows)

SELECT DISTINCT removes rows that are identical in every column you select. It can't choose between rows that differ: Ben and Benjamin share an email, but DISTINCT email, name keeps both.

To keep one row per key, number the rows within each key and keep number 1. The ORDER BY inside the window decides which one wins: here the latest sign-up, so the newest name is kept.

WITH ranked AS (
  SELECT id, LOWER(email) AS email, name,
    ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY signed_up_at DESC) AS rn
  FROM signups
)
SELECT id, email, name
FROM ranked
WHERE rn = 1
ORDER BY email;
Returns
id | email            | name
---+------------------+---------
3  | ana@example.com  | Ana
7  | ben@example.com  | Benjamin
4  | cleo@example.com | Cleo
6  | dev@example.com  | Dev
(4 rows)

To delete the extra copies from the table itself, look at what you're about to delete with a SELECT first, and keep a backup. Here each email keeps its lowest id:

DELETE FROM signups
WHERE id NOT IN (SELECT MIN(id) FROM signups GROUP BY LOWER(email));

SELECT COUNT(*) AS remaining FROM signups;
Returns
remaining
---------
4
(1 row)

Then stop them coming back: a unique constraint on the key, a load that inserts-or-updates (often called an upsert or MERGE) rather than blindly inserting, and normalised keys on the way in.

Practice

Which email addresses (exactly as stored) appear more than once? Show the email and how many times.

The tables
  • customers (id, name, city, joined)
  • products (id, name, category, price)
  • orders (id, customer_id, ordered_on, status)
  • order_items (order_id, product_id, qty)
  • signups (id, email, name, signed_up_at)
  • payments (id, order_id, amount, currency, paid_at)
More info

Quick reference

GROUP BY key HAVING COUNT(*) > 1Find the keys that repeat.
LOWER(x), TRIM(x)Clean a key: lower case, no outer spaces.
SELECT DISTINCT a, bDrops rows identical in a and b.
ROW_NUMBER() OVER (PARTITION BY key ORDER BY …) = 1Keep one row per key; the ORDER BY picks which.
UNIQUE constraint, upsert / MERGEStop duplicates at the door.

The other AI and data engineering courses