Level 5 · Find duplicate customer records

Duplicate records are one of the most common data quality issues an analyst meets. Customers sign up twice, a sync job runs twice, a CRM import repeats a batch. Each duplicate inflates “customer count” and splits one person’s history across two IDs, which deflates lifetime value and repeat rates.

Step 1: choose the key

What makes two rows “the same customer”? Here it is the email, but only after normalizing it. 'Owen.Hudson@iCloud.com ' and 'owen.hudson@icloud.com' are different strings and the same person. So the key is LOWER(TRIM(email)). Picking the key is a business decision; write down what you chose.

Step 2: group and keep groups larger than one

SELECT
  LOWER(TRIM(email)) AS email,
  COUNT(*) AS records,
  MIN(customer_id) AS first_customer_id,
  MAX(customer_id) AS last_customer_id
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

HAVING COUNT(*) > 1 is the classic duplicate finder. MIN(customer_id) gives the surviving record for a merge, assuming IDs increase over time, which Tyler can confirm with signup_date.

Step 3: see the full rows

To inspect every duplicate row, not just one line per email, join back to the table:

WITH dupes AS (
  SELECT LOWER(TRIM(email)) AS email_key
  FROM customers
  GROUP BY 1
  HAVING COUNT(*) > 1
)
SELECT c.*
FROM customers AS c
JOIN dupes AS d ON d.email_key = LOWER(TRIM(c.email))
ORDER BY d.email_key, c.customer_id;

Counting unique people

Until the records are merged, report customers as COUNT(DISTINCT LOWER(TRIM(email))) rather than COUNT(*), and say why. Harbor & Pine has 2,018 customer rows but 2,000 distinct people. A small gap here, but in a real CRM duplicates can reach 5 to 10 percent of rows.

Keeping one row per group

Finding duplicates is half the job. To pick one row per key with all its columns, the standard tool is ROW_NUMBER(), covered in the next chapter:

QUALIFY ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY customer_id) = 1

QUALIFY works in DuckDB, BigQuery and Snowflake; in Postgres and MySQL you wrap the window function in a CTE and filter in the outer query.

Common mistakes

• Grouping by the raw email and concluding “no duplicates”.
• Using SELECT DISTINCT *, which only removes rows that are identical in every column. Duplicate customers usually differ in ID and signup date, so DISTINCT removes nothing.
• Deleting duplicates without moving their orders to the surviving ID. That is an engineering decision; an analyst’s job is to find and size the problem.