AI Hints
Homeโ€บSQL Tutorialโ€บCleaning Messy Data
SQL ยท Lesson 92 of 126

Removing Duplicates

Keep one of each.

โฑ 2 min readโ–ถ Video at 2:11:05๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Ana Lopez signed up twice. Duplicates are everywhere in real data. People submit forms twice, systems retry, and spreadsheets get merged. Before we count club members, we need one row per person.

Removing Duplicates
From the video: Removing Duplicates
  • Ana signed up twice
  • Duplicates are everywhere
  • One row per person

Find the duplicates

First, find them. Clean the emails, then group by email, and keep groups with more than one row. Run it. Ana's email appears twice. Only after cleaning could we see that.

clean.sqlsql
WITH clean AS (
    SELECT TRIM(name) AS name, LOWER(TRIM(email)) AS email
    FROM signups
    WHERE NULLIF(TRIM(email), '') IS NOT NULL
)
SELECT email, COUNT(*) AS copies
FROM clean
GROUP BY email
HAVING COUNT(*) > 1;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚    email     โ”‚ copies โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ ana@mail.com โ”‚ 2      โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Keep one of each

Now keep just one. Row number, partitioned by email, numbers each copy of a person. The first copy gets one. Then we keep only row number one. Run it. Three people, each once. Cara has no email, so she needs to be checked by hand.

clean.sqlsql
WITH clean AS (
    SELECT TRIM(name) AS name, LOWER(TRIM(email)) AS email
    FROM signups
    WHERE NULLIF(TRIM(email), '') IS NOT NULL
),
numbered AS (
    SELECT name, email,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY name) AS rn
    FROM clean
)
SELECT name, email
FROM numbered
WHERE rn = 1;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   name    โ”‚    email     โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ ana lopez โ”‚ ana@mail.com โ”‚
โ”‚ Ben Ortiz โ”‚ ben@mail.com โ”‚
โ”‚ Dev Patel โ”‚ dev@mail.com โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Same name, different people

A warning. Don't remove duplicates by name alone. In Helen's real customers table, there are three customers called Daniel Smith. Run it. But they have different emails, and live in different cities. They're three different people who share a name. Always use something truly unique, like an email.

query.sqlsql
SELECT customer_id, first_name, last_name, email, city
FROM customers
WHERE first_name = 'Daniel'
  AND last_name  = 'Smith';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ customer_id โ”‚ first_name โ”‚ last_name โ”‚          email           โ”‚  city  โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ 264         โ”‚ Daniel     โ”‚ Smith     โ”‚ daniel.smith264@mail.com โ”‚ Austin โ”‚
โ”‚ 433         โ”‚ Daniel     โ”‚ Smith     โ”‚ daniel.smith433@mail.com โ”‚ Leeds  โ”‚
โ”‚ 465         โ”‚ Daniel     โ”‚ Smith     โ”‚ daniel.smith465@mail.com โ”‚ Dublin โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Clean the cities

Your turn. Show each sign up's trimmed name, and their city in upper case. If the city is missing, show the word unknown. Pause and try. Here's a solution. Trim, upper, and coalesce. Dev Patel's city shows unknown.

โœ๏ธ Exercise: Clean the cities

Trimmed name; city in UPPER CASE, or 'UNKNOWN' if missing

Show hint

COALESCE(UPPER(TRIM(city)), 'UNKNOWN')

Show solution
solution.sqlsql
SELECT TRIM(name) AS name,
       COALESCE(UPPER(TRIM(city)), 'UNKNOWN') AS city
FROM signups;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   name    โ”‚   city   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ ana lopez โ”‚ DUBLIN   โ”‚
โ”‚ Ben Ortiz โ”‚ LONDON   โ”‚
โ”‚ ana lopez โ”‚ DUBLIN   โ”‚
โ”‚ Cara Diaz โ”‚ NEW YORK โ”‚
โ”‚ Dev Patel โ”‚ UNKNOWN  โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Clean first, then group to find duplicates, and row number to keep one of each. Data cleaning is a real job skill. Next, let's get you ready for SQL interviews.

Key takeaways
  • Clean, then compare
  • ROW_NUMBER() to dedupe
  • Never dedupe by name
Next up: Interview Questions, Part 1 โ€” the classics