Lessons › SQL › Deep Dive: Data Cleaning

SQL lesson

Removing duplicates with ROW_NUMBER() and DELETE

ROW_NUMBER() assigns a unique sequence number to each row within a group of identical records, so you can safely target every copy after the first for deletion.

Example

Removing Duplicate Retail Orders with ROW_NUMBER()

WITH ranked AS (
  SELECT ctid,
         ROW_NUMBER() OVER (PARTITION BY order_id, customer_id, order_date, revenue ORDER BY ctid) AS rn
  FROM orders
)
DELETE FROM orders
WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1)

Result:

Step by step

1. WITH lets you name a sub-query so the main query can reference it.

   WITH ranked AS (
     SELECT ctid,
            ROW_NUMBER() OVER (PARTITION BY order_id, customer_id, order_date, revenue ORDER BY ctid) AS rn
     FROM orders
   )

2. DELETE FROM removes rows from a table.

   WITH ranked AS (
     SELECT ctid,
            ROW_NUMBER() OVER (PARTITION BY order_id, customer_id, order_date, revenue ORDER BY ctid) AS rn
     FROM orders
   )
   DELETE FROM orders

3. WHERE keeps only the rows that match your condition.

   WITH ranked AS (
     SELECT ctid,
            ROW_NUMBER() OVER (PARTITION BY order_id, customer_id, order_date, revenue ORDER BY ctid) AS rn
     FROM orders
   )
   DELETE FROM orders
   WHERE ctid IN (SELECT ctid FROM ranked WHERE rn > 1);

Practice

Try each one in your head first, then open the answer.

WITH ranked AS (SELECT customer_id, ROW_NUMBER() OVER (PARTITION BY phone ORDER BY customer_id) AS rn FROM customers) DELETE FROM customers WHERE customer_id IN (SELECT customer_id FROM ranked WHERE rn > 1)

WITH ranked AS (SELECT product_id, ROW_NUMBER() OVER (PARTITION BY category ORDER BY product_id) AS rn FROM products) DELETE FROM products WHERE product_id IN (SELECT product_id FROM ranked WHERE rn > 1)

WITH ranked AS (SELECT order_id, ROW_NUMBER() OVER (PARTITION BY category ORDER BY order_id) AS rn FROM orders) DELETE FROM orders WHERE order_id IN (SELECT order_id FROM ranked WHERE rn > 1)

WITH ranked AS (SELECT product_id, ROW_NUMBER() OVER (PARTITION BY warehouse ORDER BY product_id) AS rn FROM inventory) DELETE FROM inventory WHERE product_id IN (SELECT product_id FROM ranked WHERE rn > 1)

WITH ranked AS (SELECT review_id, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY review_id) AS rn FROM reviews) DELETE FROM reviews WHERE review_id IN (SELECT review_id FROM ranked WHERE rn > 1)