SQL Interview Questions and Answers Part 7 - Duplicates and Data Cleaning

SQL Interview Q&A · Part 7 of 10

In this post we are going to cover duplicates and data cleaning: finding duplicates, deleting them safely, keeping the latest version, cleaning messy text, reconciling two tables and finding orphan records. These are 7 real SQL interview questions, asked the way they come up on the job, and the last one is a scenario where the nightly load ran twice and some sales are now in the warehouse two times.

This is everyday data engineering work. Interviewers ask about it because the queries are short, but the details are not: which columns define a duplicate, which row survives, and how you make sure it never happens again.

Every query below is complete, so you can copy it, run it on the sample data and get exactly the output shown under it.

Part 7 of the SQL Server RealTime Questions Answers series on TechBrothersIT.

On this page

1. The short answer
2. Let's create some sample data
3. Question 1: The web signup form was submitted more than once, so the signups table has repeated rows. How do you find duplicates across name, email and city?
4. Question 2: You need to delete the duplicate signups but keep one row of each. How do you do it safely?
5. Question 3: A staging table has several versions of each customer, two of them on the same day. How do you keep only the latest version of each record?
6. Question 4: A raw customer file has extra spaces, mixed-case emails, empty strings and NULLs. How do you clean it in SQL before loading?
7. Question 5: The source and the warehouse both have 12 orders, but the totals don't match: 5,200 against 5,230. How do you find exactly which rows differ?
8. Question 6: A report that joins orders to customers silently loses 80 dollars of revenue. How do you find the orphan records, and how does a foreign key prevent them?
9. Scenario 7: The nightly load ran twice and some order lines are now in the warehouse two times. How do you find and remove the double-loaded rows, and make the load safe to re-run?
10. The full SQL script

The short answer

Say this first

Find duplicates with GROUP BY the duplicate key HAVING COUNT(*) > 1, and delete them or keep the latest version with ROW_NUMBER() and a deterministic tie-breaker. Clean text with TRIM, NULLIF and COALESCE before matching, reconcile with EXCEPT in both directions, find orphans with a LEFT JOIN, and make loads idempotent with a unique key and insert-only-new-rows.

Let's create some sample data

We use a small shop database called TB Shop: departments, employees, customers, products, orders, order items and payments. This part adds six small tables for the cleaning work: signups, cust_staging, raw_customers, dw_orders (a warehouse copy of orders), stg_order_lines and wh_order_lines. The script is written for PostgreSQL, and it runs on SQL Server, MySQL 8 or Snowflake with very small changes.

Sample data - SQL script (PostgreSQL)

DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');

CREATE TABLE employees (
  emp_id     INT PRIMARY KEY,
  name       VARCHAR(20),
  dept_id    INT,            -- NULL = not assigned yet
  manager_id INT,            -- NULL = top of the company
  salary     INT,
  hire_date  DATE
);
INSERT INTO employees VALUES
  (1, 'Aisha',  NULL, NULL, 150000, '2018-03-01'),
  (2, 'Omar',   20,   1,    120000, '2019-06-15'),
  (3, 'Lina',   20,   2,     90000, '2021-01-10'),
  (4, 'Ravi',   20,   2,     90000, '2022-04-05'),
  (5, 'Mei',    20,   2,     75000, '2024-09-01'),
  (6, 'Sara',   10,   1,     95000, '2020-02-20'),
  (7, 'Daniel', 10,   6,    105000, '2021-07-01'),
  (8, 'Fatima', 10,   6,     60000, '2023-11-12'),
  (9, 'John',   30,   1,     70000, '2019-09-30'),
  (10,'Zara',   NULL, 9,     55000, '2026-01-05');

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(20),
  city        VARCHAR(20),
  region      VARCHAR(10),    -- NULL = unknown
  email       VARCHAR(40)
);
INSERT INTO customers VALUES
  (1, 'Sara Khan',  'Toronto', 'East', 'sara@mail.com'),
  (2, 'Omar Ali',   'Dallas',  'West', 'omar@mail.com'),
  (3, 'Lina Park',  'Seattle', 'West', 'lina@mail.com'),
  (4, 'Mei Chen',   'Boston',  'East', 'mei@mail.com'),
  (5, 'Ravi Patel', 'Austin',  NULL,   'ravi@mail.com'),
  (6, 'Chen Wu',    'Denver',  'West', 'chen@mail.com'),
  (7, 'Sara K.',    'Toronto', 'East', 'sara@mail.com');

CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
  (1, 'Laptop',   'Electronics', 1200.00),
  (2, 'Monitor',  'Electronics',  300.00),
  (3, 'Keyboard', 'Accessories',   50.00),
  (4, 'Mouse',    'Accessories',   25.00),
  (5, 'Desk',     'Furniture',    400.00),
  (6, 'Chair',    'Furniture',    250.00);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  status      VARCHAR(10),
  amount      DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (1001, 1, '2026-01-05', 'Delivered', 1250.00),
  (1002, 2, '2026-01-18', 'Delivered',  300.00),
  (1003, 4, '2026-02-02', 'Delivered',  650.00),
  (1004, 1, '2026-02-14', 'Cancelled',   50.00),
  (1005, 3, '2026-02-20', 'Delivered', 1200.00),
  (1006, 4, '2026-03-03', 'Shipped',    275.00),
  (1007, 5, '2026-03-15', 'Delivered',  400.00),
  (1008, 2, '2026-04-01', 'Delivered',   75.00),
  (1009, 4, '2026-04-22', 'Delivered',  250.00),
  (1010, 1, '2026-05-09', 'Shipped',    300.00),
  (1011, 3, '2026-05-30', 'Cancelled',  400.00),
  (1012, 7, '2026-06-11', 'Delivered',   50.00);

CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
  (1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
  (1002, 2, 1, 300.00),
  (1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
  (1004, 3, 1, 50.00),
  (1005, 1, 1, 1200.00),
  (1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
  (1007, 5, 1, 400.00),
  (1008, 4, 3, 25.00),
  (1009, 6, 1, 250.00),
  (1010, 2, 1, 300.00),
  (1011, 5, 1, 400.00),
  (1012, 3, 1, 50.00);

CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
  (1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);

DROP TABLE IF EXISTS signups;
DROP TABLE IF EXISTS cust_staging;
DROP TABLE IF EXISTS raw_customers;
DROP TABLE IF EXISTS dw_orders;
DROP TABLE IF EXISTS stg_order_lines;
DROP TABLE IF EXISTS wh_order_lines;

-- Q1, Q2: the web sign-up form was submitted more than once
CREATE TABLE signups (signup_id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(40), city VARCHAR(20));
INSERT INTO signups VALUES
  (1, 'Sara Khan', 'sara@mail.com', 'Toronto'),
  (2, 'Omar Ali',  'omar@mail.com', 'Dallas'),
  (3, 'Sara Khan', 'sara@mail.com', 'Toronto'),
  (4, 'Lina Park', 'lina@mail.com', 'Seattle'),
  (5, 'Omar Ali',  'omar@mail.com', 'Dallas'),
  (6, 'Sara Khan', 'sara@mail.com', 'Toronto'),
  (7, 'Omar Ali',  'omar@mail.com', 'Austin'),
  (8, 'Mei Chen',  'mei@mail.com',  'Boston');

-- Q3: the CRM sends every change; Lina has two versions on the same day
CREATE TABLE cust_staging (load_id INT PRIMARY KEY, customer_id INT, city VARCHAR(20), updated_on DATE);
INSERT INTO cust_staging VALUES
  (1, 1, 'Toronto',   '2026-06-01'),
  (2, 2, 'Dallas',    '2026-06-01'),
  (3, 3, 'Seattle',   '2026-06-02'),
  (4, 1, 'Ottawa',    '2026-06-03'),
  (5, 2, 'Houston',   '2026-06-04'),
  (6, 1, 'Vancouver', '2026-06-05'),
  (7, 3, 'Portland',  '2026-06-02');

-- Q4: a raw customer file with spaces, mixed case, empty strings and NULLs
CREATE TABLE raw_customers (raw_id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(40), city VARCHAR(20), country VARCHAR(5));
INSERT INTO raw_customers VALUES
  (1, '  Sara Khan ', 'SARA@MAIL.COM ', 'Toronto', 'ca'),
  (2, 'Omar Ali',     ' omar@mail.com', '',        'US '),
  (3, 'Lina Park  ',  'Lina@Mail.com',  '   ',     ' us'),
  (4, 'Mei Chen',     NULL,             NULL,      'US');

-- Q5, Q6: the warehouse copy of orders (no foreign key): it missed order 1012,
-- kept an old status for 1006, and has a test order for customer 9, who does not exist
CREATE TABLE dw_orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE, status VARCHAR(10), amount DECIMAL(10,2));
INSERT INTO dw_orders SELECT order_id, customer_id, order_date, status, amount FROM orders WHERE order_id <> 1012;
UPDATE dw_orders SET status = 'Delivered' WHERE order_id = 1006;
INSERT INTO dw_orders VALUES (1099, 9, '2026-06-20', 'Delivered', 80.00);

-- Q7: last night's file, and the warehouse table it loads (run 102 and, by mistake, run 103)
CREATE TABLE stg_order_lines (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO stg_order_lines VALUES (1010, 2, 1, 300.00), (1011, 5, 1, 400.00), (1012, 3, 1, 50.00);
CREATE TABLE wh_order_lines (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2), run_id INT);
INSERT INTO wh_order_lines VALUES
  (1008, 4, 3,  25.00, 101), (1009, 6, 1, 250.00, 101),
  (1010, 2, 1, 300.00, 102), (1011, 5, 1, 400.00, 102), (1012, 3, 1, 50.00, 102),
  (1010, 2, 1, 300.00, 103), (1011, 5, 1, 400.00, 103), (1012, 3, 1, 50.00, 103);

Here are the signups. Each row has its own signup_id, but Sara Khan in Toronto appears three times and Omar Ali in Dallas twice. Omar Ali in Austin, signup 7, has the same name and email but a different city.

signups8 rows

signup_id	name	email	city
1	Sara Khan	sara@mail.com	Toronto
2	Omar Ali	omar@mail.com	Dallas
3	Sara Khan	sara@mail.com	Toronto
4	Lina Park	lina@mail.com	Seattle
5	Omar Ali	omar@mail.com	Dallas
6	Sara Khan	sara@mail.com	Toronto
7	Omar Ali	omar@mail.com	Austin
8	Mei Chen	mei@mail.com	Boston

Question 1: The web signup form was submitted more than once, so the signups table has repeated rows. How do you find duplicates across name, email and city?

GROUP BY every column that defines a duplicate, and keep the groups with HAVING COUNT(*) > 1. The surrogate ID is not part of the key.

SQL - Find duplicate keys: GROUP BY every column that defines a duplicate

SELECT name, email, city, COUNT(*) AS copies
FROM signups
GROUP BY name, email, city
HAVING COUNT(*) > 1
ORDER BY name;

Output (PostgreSQL 16)2 rows

name	email	city	copies
Omar Ali	omar@mail.com	Dallas	2
Sara Khan	sara@mail.com	Toronto	3

Sara Khan in Toronto has 3 copies, and Omar Ali in Dallas has 2. Omar in Austin is not flagged: same name and email, different city. So the first thing to agree with the business is the duplicate key, the columns that make two rows the same.

GROUP BY tells you which keys are duplicated, but to fix them you need the rows. COUNT(*) OVER (PARTITION BY ...) puts the copy count on every row, without collapsing the rows.

SQL - Show the duplicate rows themselves with COUNT(*) OVER

SELECT signup_id, name, email, city, copies
FROM (SELECT signup_id, name, email, city,
             COUNT(*) OVER (PARTITION BY name, email, city) AS copies
      FROM signups) d
WHERE copies > 1
ORDER BY name, signup_id;

Output (PostgreSQL 16)5 rows

signup_id	name	email	city	copies
2	Omar Ali	omar@mail.com	Dallas	2
5	Omar Ali	omar@mail.com	Dallas	2
1	Sara Khan	sara@mail.com	Toronto	3
3	Sara Khan	sara@mail.com	Toronto	3
6	Sara Khan	sara@mail.com	Toronto	3

Now we can see all five signup IDs involved: 2 and 5 for Omar, and 1, 3 and 6 for Sara.

Question 2: You need to delete the duplicate signups but keep one row of each. How do you do it safely?

Number the rows inside each duplicate group with ROW_NUMBER, partitioned by the duplicate key, and delete every row whose number is greater than 1. The ORDER BY decides the survivor. Here we keep the lowest signup_id, the first submission.

The delete matches the rows by their unique signup_id. Always run the inner SELECT on its own first, and check exactly what you are about to delete.

SQL - Delete duplicates, keep the first row of each group

DELETE FROM signups
WHERE signup_id IN (
  SELECT signup_id
  FROM (SELECT signup_id,
               ROW_NUMBER() OVER (PARTITION BY name, email, city
                                  ORDER BY signup_id) AS rn
        FROM signups) d
  WHERE rn > 1
);

The statement runs and changes the data. The check query below shows the result.

SQL - check the data after the change

SELECT signup_id, name, email, city
FROM signups
ORDER BY signup_id;

Output after the change5 rows

signup_id	name	email	city
1	Sara Khan	sara@mail.com	Toronto
2	Omar Ali	omar@mail.com	Dallas
4	Lina Park	lina@mail.com	Seattle
7	Omar Ali	omar@mail.com	Austin
8	Mei Chen	mei@mail.com	Boston

Signups 3, 5 and 6 are gone. One Sara, one Omar in Dallas, and Omar in Austin, signup 7, stays, because he was never a duplicate. Five rows are left.

Two tips

In SQL Server you can delete straight from a CTE: WITH d AS (...) DELETE FROM d WHERE rn > 1. And if the table has no unique ID at all, add one first, or copy the DISTINCT rows into a new table and swap them.

Question 3: A staging table has several versions of each customer, two of them on the same day. How do you keep only the latest version of each record?

ROW_NUMBER, partitioned by the customer, ordered by the update date descending, with a tie-breaker. Row 1 is the latest version, so we keep rn = 1.

The source sends every change, and the target wants one current row per customer. Here is the staging table: customer 1, Sara, has three versions.

cust_staging7 rows

load_id	customer_id	city	updated_on
1	1	Toronto	2026-06-01
2	2	Dallas	2026-06-01
3	3	Seattle	2026-06-02
4	1	Ottawa	2026-06-03
5	2	Houston	2026-06-04
6	1	Vancouver	2026-06-05
7	3	Portland	2026-06-02

SQL - Keep only the latest version of each customer (with a tie-breaker)

SELECT customer_id, city, updated_on, load_id
FROM (SELECT customer_id, city, updated_on, load_id,
             ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY updated_on DESC, load_id DESC) AS rn
      FROM cust_staging) v
WHERE rn = 1
ORDER BY customer_id;

Output (PostgreSQL 16)3 rows

customer_id	city	updated_on	load_id
1	Vancouver	2026-06-05	6
2	Houston	2026-06-04	5
3	Portland	2026-06-02	7

Customer 1 ends up in Vancouver, from 2026-06-05, and customer 2 in Houston. Why not MAX(updated_on) with GROUP BY? Because MAX gives you the date, not the rest of the row. You would have to join back, and that join breaks on ties.

And look at customer 3, Lina: two versions on 2026-06-02, Seattle and Portland. Without a tie-breaker the database can pick either one, and the report changes between runs. load_id DESC makes it deterministic: load 7, Portland, wins.

QUALIFY

In Snowflake, Databricks and DuckDB, the same filter is QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY updated_on DESC, load_id DESC) = 1, without the subquery.

Question 4: A raw customer file has extra spaces, mixed-case emails, empty strings and NULLs. How do you clean it in SQL before loading?

TRIM the spaces, LOWER the emails, UPPER the codes, and turn empty strings into real NULLs with NULLIF, so one COALESCE default covers everything.

Here is the raw file. It has leading and trailing spaces, mixed case emails, an empty city, a blank city of only spaces, a NULL city and lowercase country codes.

raw_customers4 rows

name	email	city	country
Sara Khan	SARA@MAIL.COM	Toronto	ca
Omar Ali	omar@mail.com		US
Lina Park	Lina@Mail.com		us
Mei Chen	NULL	NULL	US

SQL - Clean messy text: TRIM, LOWER, UPPER, NULLIF and COALESCE

SELECT raw_id,
       TRIM(name)                                  AS name,
       LOWER(TRIM(email))                          AS email,
       COALESCE(NULLIF(TRIM(city), ''), 'Unknown') AS city,
       UPPER(TRIM(country))                        AS country
FROM raw_customers
ORDER BY raw_id;

Output (PostgreSQL 16)4 rows

raw_id	name	email	city	country
1	Sara Khan	sara@mail.com	Toronto	CA
2	Omar Ali	omar@mail.com	Unknown	US
3	Lina Park	lina@mail.com	Unknown	US
4	Mei Chen	NULL	Unknown	US

The names lost their leading and trailing spaces, SARA@MAIL.COM became sara@mail.com, and ca became CA. The key move is NULLIF(TRIM(city), ''): Omar's empty city and Lina's blank city become a real NULL, and then COALESCE gives all three, including Mei's NULL, the same Unknown. Without NULLIF, the empty strings would slip through as a city with no name.

Notice Mei's email stays NULL. Do not invent defaults for values you match on.

Order matters

Clean before you deduplicate. SARA@MAIL.COM and sara@mail.com are one person, but a GROUP BY sees two.

Question 5: The source and the warehouse both have 12 orders, but the totals don't match: 5,200 against 5,230. How do you find exactly which rows differ?

Start cheap with counts and totals, then use EXCEPT in both directions to get the exact rows. Let's compare orders with the warehouse copy, dw_orders.

SQL - Reconcile, step 1: compare row counts and totals

SELECT 'source' AS side, COUNT(*) AS row_count, SUM(amount) AS total
FROM orders
UNION ALL
SELECT 'target', COUNT(*), SUM(amount)
FROM dw_orders;

Output (PostgreSQL 16)2 rows

side	row_count	total
source	12	5200.00
target	12	5230.00

12 rows on both sides, so the counts match. But the totals do not: 5200.00 against 5230.00. Matching counts prove nothing.

EXCEPT returns the rows of the first query that are not in the second, comparing every selected column. We run it both ways and label each side.

SQL - Reconcile, step 2: EXCEPT in both directions

SELECT order_id, 'only in source' AS side, status, amount
FROM (SELECT order_id, status, amount FROM orders
      EXCEPT
      SELECT order_id, status, amount FROM dw_orders) s
UNION ALL
SELECT order_id, 'only in target', status, amount
FROM (SELECT order_id, status, amount FROM dw_orders
      EXCEPT
      SELECT order_id, status, amount FROM orders) t
ORDER BY order_id, side;

Output (PostgreSQL 16)4 rows

order_id	side	status	amount
1006	only in source	Shipped	275.00
1006	only in target	Delivered	275.00
1012	only in source	Delivered	50.00
1099	only in target	Delivered	80.00

Four rows tell the whole story. Order 1012 is only in the source: the load missed it. Order 1099 is only in the target. And order 1006 appears on both sides: same ID, Shipped in the source and Delivered in the target. That is a change the warehouse never picked up.

Interview details

EXCEPT treats NULLs as equal, so it is safer than comparing columns in a join. Oracle before 21c calls it MINUS. If you only care about missing keys, use NOT EXISTS on the ID.

Question 6: A report that joins orders to customers silently loses 80 dollars of revenue. How do you find the orphan records, and how does a foreign key prevent them?

LEFT JOIN the orders to customers and keep the rows where the customer side is NULL. It is the same anti join as finding customers with no orders, pointed the other way.

SQL - Find orphan orders: LEFT JOIN, keep the rows with no customer

SELECT o.order_id, o.customer_id, o.amount
FROM dw_orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;

Output (PostgreSQL 16)1 row

order_id	customer_id	amount
1099	9	80.00

One orphan: order 1099, for customer 9, who is not in the customers table. Any report that inner joins orders to customers silently drops its 80.00.

A foreign key prevents this, because the database rejects any order whose customer does not exist. Let's try to add one now.

SQL - A foreign key cannot even be added while the orphan exists

ALTER TABLE dw_orders
  ADD CONSTRAINT fk_dw_orders_customer
  FOREIGN KEY (customer_id)
  REFERENCES customers (customer_id);

Error - this query is meant to failinsert or update on table "dw_orders" violates foreign key constraint "fk_dw_orders_customer" DETAIL: Key (customer_id)=(9) is not present in table "customers".

It fails with insert or update on table "dw_orders" violates foreign key constraint "fk_dw_orders_customer". The constraint is not created: PostgreSQL checks every existing row when you add the constraint, and the detail names the one that breaks it, Key (customer_id)=(9) is not present in table "customers". The orphan is already there, so you have to fix the data before you can add the constraint.

In the warehouse

In Snowflake and BigQuery, foreign keys are usually informational, not enforced. The pipeline runs this orphan check itself and maps unknown customers to an unknown member row, instead of losing the sales.

Scenario 7: The nightly load ran twice and some order lines are now in the warehouse two times. How do you find and remove the double-loaded rows, and make the load safe to re-run?

A manager says the nightly load ran twice and yesterday's sales are doubled: remove the extra rows, and make sure it cannot happen again. Here is the warehouse table, where every row carries the run_id that loaded it.

wh_order_lines8 rows

order_id	product_id	qty	unit_price	run_id
1008	4	3	25.00	101
1009	6	1	250.00	101
1010	2	1	300.00	102
1010	2	1	300.00	103
1011	5	1	400.00	103
1011	5	1	400.00	102
1012	3	1	50.00	103
1012	3	1	50.00	102

First, prove it. GROUP BY the business key of an order line, order_id and product_id, and keep the keys with more than one copy.

SQL - Scenario: find the double-loaded lines by their business key

SELECT order_id, product_id, COUNT(*) AS copies,
       MIN(run_id) AS first_run, MAX(run_id) AS last_run
FROM wh_order_lines
GROUP BY order_id, product_id
HAVING COUNT(*) > 1
ORDER BY order_id, product_id;

Output (PostgreSQL 16)3 rows

order_id	product_id	copies	first_run	last_run
1010	2	2	102	103
1011	5	2	102	103
1012	3	2	102	103

Three keys, 2 copies each. All three lines of run 102 came in again with run 103.

Now delete only the run 103 rows whose key an earlier run already loaded. That way, if run 103 had also brought new lines, they stay.

SQL - Remove only the copies: run 103 rows whose key an earlier run already loaded

DELETE FROM wh_order_lines
WHERE run_id = 103
  AND EXISTS (
    SELECT 1 FROM wh_order_lines e
    WHERE e.order_id   = wh_order_lines.order_id
      AND e.product_id = wh_order_lines.product_id
      AND e.run_id     < wh_order_lines.run_id);

The statement runs and changes the data. The check query below shows the result.

SQL - check the data after the change

SELECT run_id, COUNT(*) AS line_rows
FROM wh_order_lines
GROUP BY run_id
ORDER BY run_id;

Output after the change2 rows

run_id	line_rows
101	2
102	3

Run 101 keeps 2 rows and run 102 keeps 3. No run ID column? Use the ROW_NUMBER delete from question 2. In MySQL, the delete target cannot be used in its own subquery, so write it as DELETE ... JOIN.

Next, the safety net: a unique index on the business key.

SQL - Safety net: a unique index on the business key

CREATE UNIQUE INDEX ux_order_line
  ON wh_order_lines (order_id, product_id);

The statement runs without returning rows.

Let's load the same file again with a plain INSERT.

SQL - Re-running a plain INSERT is now rejected

INSERT INTO wh_order_lines
SELECT order_id, product_id, qty, unit_price, 104
FROM stg_order_lines;

Error - this query is meant to failduplicate key value violates unique constraint "ux_order_line" DETAIL: Key (order_id, product_id)=(1010, 2) already exists.

The database rejects it: duplicate key value violates unique constraint "ux_order_line", and the detail names the first duplicate, Key (order_id, product_id)=(1010, 2) already exists. The load fails loudly instead of silently doubling revenue.

Finally, make the load idempotent: insert only the lines that are not there yet, with NOT EXISTS on the key.

SQL - Idempotent load: insert only the lines that are not there yet

INSERT INTO wh_order_lines
SELECT s.order_id, s.product_id, s.qty, s.unit_price, 104
FROM stg_order_lines s
WHERE NOT EXISTS (
  SELECT 1 FROM wh_order_lines w
  WHERE w.order_id = s.order_id
    AND w.product_id = s.product_id);

The statement runs and changes the data. The check query below shows the result.

SQL - check the data after the change

SELECT COUNT(*) AS total_rows
FROM wh_order_lines;

Output after the change1 row

total_rows
5

Run it once or ten times, the table still has 5 rows. The same load can be a MERGE ... WHEN NOT MATCHED THEN INSERT in SQL Server, Snowflake or PostgreSQL 15+, or INSERT ... ON CONFLICT DO NOTHING in PostgreSQL.

Key points to remember

• GROUP BY the duplicate key, HAVING COUNT(*) > 1
• ROW_NUMBER() to delete duplicates or keep the latest, with a tie-breaker
• TRIM, NULLIF, COALESCE: clean before you match
• EXCEPT both ways to reconcile; LEFT JOIN for orphans
• Loads must be idempotent: unique key + insert only new rows

The full SQL script

Here is everything in one script: the sample data and every query from this post, in order. Copy it and run it top to bottom.

Show the full SQL script (sample data and every query)

Full script - copy and run top to bottom

-- ========================================================================
-- SQL Interview Q&A #7 - Duplicates and data cleaning
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16 (and cross-checked on DuckDB). Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example:  CREATE SCHEMA sql_q07;  SET search_path TO sql_q07;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS departments;

CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(20));
INSERT INTO departments VALUES (10, 'Sales'), (20, 'Engineering'), (30, 'HR'), (40, 'Finance');

CREATE TABLE employees (
  emp_id     INT PRIMARY KEY,
  name       VARCHAR(20),
  dept_id    INT,            -- NULL = not assigned yet
  manager_id INT,            -- NULL = top of the company
  salary     INT,
  hire_date  DATE
);
INSERT INTO employees VALUES
  (1, 'Aisha',  NULL, NULL, 150000, '2018-03-01'),
  (2, 'Omar',   20,   1,    120000, '2019-06-15'),
  (3, 'Lina',   20,   2,     90000, '2021-01-10'),
  (4, 'Ravi',   20,   2,     90000, '2022-04-05'),
  (5, 'Mei',    20,   2,     75000, '2024-09-01'),
  (6, 'Sara',   10,   1,     95000, '2020-02-20'),
  (7, 'Daniel', 10,   6,    105000, '2021-07-01'),
  (8, 'Fatima', 10,   6,     60000, '2023-11-12'),
  (9, 'John',   30,   1,     70000, '2019-09-30'),
  (10,'Zara',   NULL, 9,     55000, '2026-01-05');

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name        VARCHAR(20),
  city        VARCHAR(20),
  region      VARCHAR(10),    -- NULL = unknown
  email       VARCHAR(40)
);
INSERT INTO customers VALUES
  (1, 'Sara Khan',  'Toronto', 'East', 'sara@mail.com'),
  (2, 'Omar Ali',   'Dallas',  'West', 'omar@mail.com'),
  (3, 'Lina Park',  'Seattle', 'West', 'lina@mail.com'),
  (4, 'Mei Chen',   'Boston',  'East', 'mei@mail.com'),
  (5, 'Ravi Patel', 'Austin',  NULL,   'ravi@mail.com'),
  (6, 'Chen Wu',    'Denver',  'West', 'chen@mail.com'),
  (7, 'Sara K.',    'Toronto', 'East', 'sara@mail.com');

CREATE TABLE products (product_id INT PRIMARY KEY, product VARCHAR(20), category VARCHAR(15), price DECIMAL(10,2));
INSERT INTO products VALUES
  (1, 'Laptop',   'Electronics', 1200.00),
  (2, 'Monitor',  'Electronics',  300.00),
  (3, 'Keyboard', 'Accessories',   50.00),
  (4, 'Mouse',    'Accessories',   25.00),
  (5, 'Desk',     'Furniture',    400.00),
  (6, 'Chair',    'Furniture',    250.00);

CREATE TABLE orders (
  order_id    INT PRIMARY KEY,
  customer_id INT,
  order_date  DATE,
  status      VARCHAR(10),
  amount      DECIMAL(10,2)
);
INSERT INTO orders VALUES
  (1001, 1, '2026-01-05', 'Delivered', 1250.00),
  (1002, 2, '2026-01-18', 'Delivered',  300.00),
  (1003, 4, '2026-02-02', 'Delivered',  650.00),
  (1004, 1, '2026-02-14', 'Cancelled',   50.00),
  (1005, 3, '2026-02-20', 'Delivered', 1200.00),
  (1006, 4, '2026-03-03', 'Shipped',    275.00),
  (1007, 5, '2026-03-15', 'Delivered',  400.00),
  (1008, 2, '2026-04-01', 'Delivered',   75.00),
  (1009, 4, '2026-04-22', 'Delivered',  250.00),
  (1010, 1, '2026-05-09', 'Shipped',    300.00),
  (1011, 3, '2026-05-30', 'Cancelled',  400.00),
  (1012, 7, '2026-06-11', 'Delivered',   50.00);

CREATE TABLE order_items (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO order_items VALUES
  (1001, 1, 1, 1200.00), (1001, 3, 1, 50.00),
  (1002, 2, 1, 300.00),
  (1003, 5, 1, 400.00), (1003, 6, 1, 250.00),
  (1004, 3, 1, 50.00),
  (1005, 1, 1, 1200.00),
  (1006, 6, 1, 250.00), (1006, 4, 1, 25.00),
  (1007, 5, 1, 400.00),
  (1008, 4, 3, 25.00),
  (1009, 6, 1, 250.00),
  (1010, 2, 1, 300.00),
  (1011, 5, 1, 400.00),
  (1012, 3, 1, 50.00);

CREATE TABLE payments (payment_id INT PRIMARY KEY, order_id INT, paid_amount DECIMAL(10,2));
INSERT INTO payments VALUES
  (1, 1001, 1250.00), (2, 1002, 300.00), (3, 1003, 400.00), (4, 1003, 250.00), (5, 1005, 1200.00);

DROP TABLE IF EXISTS signups;
DROP TABLE IF EXISTS cust_staging;
DROP TABLE IF EXISTS raw_customers;
DROP TABLE IF EXISTS dw_orders;
DROP TABLE IF EXISTS stg_order_lines;
DROP TABLE IF EXISTS wh_order_lines;

-- Q1, Q2: the web sign-up form was submitted more than once
CREATE TABLE signups (signup_id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(40), city VARCHAR(20));
INSERT INTO signups VALUES
  (1, 'Sara Khan', 'sara@mail.com', 'Toronto'),
  (2, 'Omar Ali',  'omar@mail.com', 'Dallas'),
  (3, 'Sara Khan', 'sara@mail.com', 'Toronto'),
  (4, 'Lina Park', 'lina@mail.com', 'Seattle'),
  (5, 'Omar Ali',  'omar@mail.com', 'Dallas'),
  (6, 'Sara Khan', 'sara@mail.com', 'Toronto'),
  (7, 'Omar Ali',  'omar@mail.com', 'Austin'),
  (8, 'Mei Chen',  'mei@mail.com',  'Boston');

-- Q3: the CRM sends every change; Lina has two versions on the same day
CREATE TABLE cust_staging (load_id INT PRIMARY KEY, customer_id INT, city VARCHAR(20), updated_on DATE);
INSERT INTO cust_staging VALUES
  (1, 1, 'Toronto',   '2026-06-01'),
  (2, 2, 'Dallas',    '2026-06-01'),
  (3, 3, 'Seattle',   '2026-06-02'),
  (4, 1, 'Ottawa',    '2026-06-03'),
  (5, 2, 'Houston',   '2026-06-04'),
  (6, 1, 'Vancouver', '2026-06-05'),
  (7, 3, 'Portland',  '2026-06-02');

-- Q4: a raw customer file with spaces, mixed case, empty strings and NULLs
CREATE TABLE raw_customers (raw_id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(40), city VARCHAR(20), country VARCHAR(5));
INSERT INTO raw_customers VALUES
  (1, '  Sara Khan ', 'SARA@MAIL.COM ', 'Toronto', 'ca'),
  (2, 'Omar Ali',     ' omar@mail.com', '',        'US '),
  (3, 'Lina Park  ',  'Lina@Mail.com',  '   ',     ' us'),
  (4, 'Mei Chen',     NULL,             NULL,      'US');

-- Q5, Q6: the warehouse copy of orders (no foreign key): it missed order 1012,
-- kept an old status for 1006, and has a test order for customer 9, who does not exist
CREATE TABLE dw_orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE, status VARCHAR(10), amount DECIMAL(10,2));
INSERT INTO dw_orders SELECT order_id, customer_id, order_date, status, amount FROM orders WHERE order_id <> 1012;
UPDATE dw_orders SET status = 'Delivered' WHERE order_id = 1006;
INSERT INTO dw_orders VALUES (1099, 9, '2026-06-20', 'Delivered', 80.00);

-- Q7: last night's file, and the warehouse table it loads (run 102 and, by mistake, run 103)
CREATE TABLE stg_order_lines (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2));
INSERT INTO stg_order_lines VALUES (1010, 2, 1, 300.00), (1011, 5, 1, 400.00), (1012, 3, 1, 50.00);
CREATE TABLE wh_order_lines (order_id INT, product_id INT, qty INT, unit_price DECIMAL(10,2), run_id INT);
INSERT INTO wh_order_lines VALUES
  (1008, 4, 3,  25.00, 101), (1009, 6, 1, 250.00, 101),
  (1010, 2, 1, 300.00, 102), (1011, 5, 1, 400.00, 102), (1012, 3, 1, 50.00, 102),
  (1010, 2, 1, 300.00, 103), (1011, 5, 1, 400.00, 103), (1012, 3, 1, 50.00, 103);


-- Step 1: Find duplicate keys: GROUP BY every column that defines a duplicate
SELECT name, email, city, COUNT(*) AS copies
FROM signups
GROUP BY name, email, city
HAVING COUNT(*) > 1
ORDER BY name;

-- Step 2: Show the duplicate rows themselves with COUNT(*) OVER
SELECT signup_id, name, email, city, copies
FROM (SELECT signup_id, name, email, city,
             COUNT(*) OVER (PARTITION BY name, email, city) AS copies
      FROM signups) d
WHERE copies > 1
ORDER BY name, signup_id;

-- Step 3: Delete duplicates, keep the first row of each group
DELETE FROM signups
WHERE signup_id IN (
  SELECT signup_id
  FROM (SELECT signup_id,
               ROW_NUMBER() OVER (PARTITION BY name, email, city
                                  ORDER BY signup_id) AS rn
        FROM signups) d
  WHERE rn > 1
);

-- check the result
SELECT signup_id, name, email, city
FROM signups
ORDER BY signup_id;

-- Step 4: Keep only the latest version of each customer (with a tie-breaker)
SELECT customer_id, city, updated_on, load_id
FROM (SELECT customer_id, city, updated_on, load_id,
             ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY updated_on DESC, load_id DESC) AS rn
      FROM cust_staging) v
WHERE rn = 1
ORDER BY customer_id;

-- Step 5: Clean messy text: TRIM, LOWER, UPPER, NULLIF and COALESCE
SELECT raw_id,
       TRIM(name)                                  AS name,
       LOWER(TRIM(email))                          AS email,
       COALESCE(NULLIF(TRIM(city), ''), 'Unknown') AS city,
       UPPER(TRIM(country))                        AS country
FROM raw_customers
ORDER BY raw_id;

-- Step 6: Reconcile, step 1: compare row counts and totals
SELECT 'source' AS side, COUNT(*) AS row_count, SUM(amount) AS total
FROM orders
UNION ALL
SELECT 'target', COUNT(*), SUM(amount)
FROM dw_orders;

-- Step 7: Reconcile, step 2: EXCEPT in both directions
SELECT order_id, 'only in source' AS side, status, amount
FROM (SELECT order_id, status, amount FROM orders
      EXCEPT
      SELECT order_id, status, amount FROM dw_orders) s
UNION ALL
SELECT order_id, 'only in target', status, amount
FROM (SELECT order_id, status, amount FROM dw_orders
      EXCEPT
      SELECT order_id, status, amount FROM orders) t
ORDER BY order_id, side;

-- Step 8: Find orphan orders: LEFT JOIN, keep the rows with no customer
SELECT o.order_id, o.customer_id, o.amount
FROM dw_orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;

-- Step 9: A foreign key cannot even be added while the orphan exists
-- This statement is EXPECTED TO FAIL: it shows the constraint protecting the data.
-- Remove the leading -- to run it and see the error.
-- ALTER TABLE dw_orders
--   ADD CONSTRAINT fk_dw_orders_customer
--   FOREIGN KEY (customer_id)
--   REFERENCES customers (customer_id);

-- Step 10: Scenario: find the double-loaded lines by their business key
SELECT order_id, product_id, COUNT(*) AS copies,
       MIN(run_id) AS first_run, MAX(run_id) AS last_run
FROM wh_order_lines
GROUP BY order_id, product_id
HAVING COUNT(*) > 1
ORDER BY order_id, product_id;

-- Step 11: Remove only the copies: run 103 rows whose key an earlier run already loaded
DELETE FROM wh_order_lines
WHERE run_id = 103
  AND EXISTS (
    SELECT 1 FROM wh_order_lines e
    WHERE e.order_id   = wh_order_lines.order_id
      AND e.product_id = wh_order_lines.product_id
      AND e.run_id     < wh_order_lines.run_id);

-- check the result
SELECT run_id, COUNT(*) AS line_rows
FROM wh_order_lines
GROUP BY run_id
ORDER BY run_id;

-- Step 12: Safety net: a unique index on the business key
CREATE UNIQUE INDEX ux_order_line
  ON wh_order_lines (order_id, product_id);

-- Step 13: Re-running a plain INSERT is now rejected
-- This statement is EXPECTED TO FAIL: it shows the constraint protecting the data.
-- Remove the leading -- to run it and see the error.
-- INSERT INTO wh_order_lines
-- SELECT order_id, product_id, qty, unit_price, 104
-- FROM stg_order_lines;

-- Step 14: Idempotent load: insert only the lines that are not there yet
INSERT INTO wh_order_lines
SELECT s.order_id, s.product_id, s.qty, s.unit_price, 104
FROM stg_order_lines s
WHERE NOT EXISTS (
  SELECT 1 FROM wh_order_lines w
  WHERE w.order_id = s.order_id
    AND w.product_id = s.product_id);

-- check the result
SELECT COUNT(*) AS total_rows
FROM wh_order_lines;

Next in this series

Part 8: Dates, Gaps and Islands

The sample data on this page is invented for teaching. Every output table is the real result of the query above it, run on PostgreSQL 16 over that sample data, and the portable queries were also checked on a second engine. Where SQL Server syntax is different, the post says so.