Level 2 · Keep one record per person

Chapter 7 found duplicate customers with GROUP BY ... HAVING COUNT(*) > 1. That tells you which emails repeat, but a group-by collapses the rows, so you cannot easily keep one whole record and drop the rest. ROW_NUMBER can.

The dedupe pattern

Number the rows inside each group of duplicates, in the order that decides the winner, and keep number 1:

WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY LOWER(TRIM(email))      -- what makes two rows the same person
      ORDER BY signup_date, customer_id    -- who wins: earliest, then lowest id
    ) AS rn
  FROM customers
)
SELECT * FROM ranked WHERE rn = 1;

Three decisions are hidden in that window, and each one should be deliberate:

• The key in PARTITION BY. Here a cleaned email. On this data the raw email column has 2,012 distinct values, the cleaned one 2,000.
• The winner in ORDER BY. Earliest signup is a common rule. Others are “the record with the most orders” or “the most recently updated”.
• A tie-breaker. customer_id makes the result the same every run.

The query above returns 2,000 rows from 2,018: one per person.

Keep and map

Deleting duplicates is rarely enough. Orders, tickets and emails may point at the dropped ids, so the CRM team needs a map from each dropped id to the id that survives. Join the ranked rows to themselves, duplicates to the winner with the same key. For example, customer 2014 (BJONES76@GMAIL.COM) maps to customer 1698 (bjones76@gmail.com). In this dataset none of the 18 duplicates has an order yet, so the merge loses nothing.

Common mistakes

• Partitioning by the raw column. The starter does this and finds only 6 duplicates, the exact copies. The other 12 differ by case or by a leading space.
• Using RANK instead of ROW_NUMBER. Two records with the same signup date would both get rank 1 and both survive.
• SELECT DISTINCT. It only removes rows that are identical in every column. Duplicate people almost always differ somewhere (the id, at least).
• Filtering rn in the same SELECT. A window value cannot go in that query’s WHERE. Compute it in a CTE and filter outside. DuckDB, Snowflake and BigQuery also accept QUALIFY rn = 1.

In SQL Server

The pattern is identical, and it is a favorite in T-SQL courses and interviews. SQL Server has no QUALIFY, so the CTE is required. A CTE can even delete the duplicates in place:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY LOWER(TRIM(email)) ORDER BY signup_date, customer_id) AS rn
  FROM customers
)
DELETE FROM ranked WHERE rn > 1;