Removing Duplicates
Keep one of each.
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.

- 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.
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;โโโโโโโโโโโโโโโโฌโโโโโโโโโ โ 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.
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;โโโโโโโโโโโโโฌโโโโโโโโโโโโโโโ โ 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.
SELECT customer_id, first_name, last_name, email, city
FROM customers
WHERE first_name = 'Daniel'
AND last_name = 'Smith';โโโโโโโโโโโโโโโฌโโโโโโโโโโโโโฌโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโ โ 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.
Trimmed name; city in UPPER CASE, or 'UNKNOWN' if missing
Show hint
COALESCE(UPPER(TRIM(city)), 'UNKNOWN')
Show solution
SELECT TRIM(name) AS name,
COALESCE(UPPER(TRIM(city)), 'UNKNOWN') AS city
FROM signups;โโโโโโโโโโโโโฌโโโโโโโโโโโ โ 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