SQL Data Cleaning Interview Questions With Worked Answers

SQL data cleaning interview questions test whether you can turn a messy table into a trustworthy one without losing rows you cannot account for. The eight questions below are common patterns in analyst and analytics engineering rounds, each with a small sample table (all created in the setup block below), a worked answer in DuckDB SQL, and the output the query returns. Every query was run on DuckDB 1.5 before publishing.

Each answer has two parts: a correct query, and the rule behind it. A stated rule, such as "ties go to the higher record id so reruns are stable", makes even a slightly clumsy query easy to defend.

Key Takeaways

• Keep-the-latest-row questions want row_number() with a full, deterministic ORDER BY, including a tie-breaker and a stated rule for NULL timestamps.
• Use TRY_CAST to parse messy text, then give every value that fails a reason instead of letting it vanish as NULL.
• Map categories with a join to a mapping table, and report unmapped values rather than guessing.
• An anti join is a join that keeps only the rows with no match. Use one to find orphans, and remember that NULL keys never match with = in SQL.
• Finish with a reconciliation: rows in must equal clean rows plus quarantined rows.

How to answer a SQL cleaning question out loud

Before writing SQL, spend 20 seconds on three sentences: what one row represents, what rule you will apply, and how you will check the result. If you skip them, expect the interviewer to ask. The SQL interview questions page lists more prompts in this style.

Setup: the sample tables

Run this once in DuckDB (the CLI, Python's duckdb.sql() or any client) to create every table used below.

CREATE TABLE customers_raw AS
SELECT customer_id, email, updated_at::TIMESTAMP AS updated_at, record_id
FROM (VALUES
  ('C1', 'ana@example.com',   '2026-01-02 09:00:00', 1),
  ('C1', 'ana.b@example.com', '2026-03-15 14:30:00', 2),
  ('C2', 'ben@example.com',   '2026-02-01 08:00:00', 3),
  ('C2', 'ben@example.com',   '2026-02-01 08:00:00', 4),
  ('C3', 'cy@example.com',    NULL,                  5)
) t(customer_id, email, updated_at, record_id);

CREATE TABLE payments_raw AS SELECT * FROM (VALUES
  (1, ' 1,250.00 '), (2, '$40'), (3, '(15.00)'), (4, 'n/a'), (5, ''), (6, '12.5.0')
) t(payment_id, amount_text);

CREATE TABLE tickets AS SELECT * FROM (VALUES
  (1, 'High'), (2, 'HIGH '), (3, 'hi'), (4, 'Low'), (5, 'urgent!!'), (6, NULL)
) t(ticket_id, priority_raw);

CREATE TABLE priority_map AS SELECT * FROM (VALUES
  ('high', 'high'), ('hi', 'high'), ('low', 'low'), ('medium', 'medium')
) t(variant, priority);

CREATE TABLE orders AS SELECT * FROM (VALUES
  (101, 'C1', 40.00), (102, 'C2', 15.50), (103, 'C9', 22.00), (104, NULL, 9.99)
) t(order_id, customer_id, amount);

CREATE TABLE customers AS SELECT * FROM (VALUES
  ('C1', 'Ana'), ('C2', 'Ben'), ('C3', 'Cy')
) t(customer_id, name);

CREATE TABLE signups AS SELECT * FROM (VALUES
  (1, '2026-03-04', 'US'), (2, '04/03/2026', 'US'), (3, '04/03/2026', 'GB'),
  (4, '2026-13-01', 'GB'), (5, 'soon', 'US')
) t(user_id, signup_text, country);

CREATE TABLE addresses AS SELECT * FROM (VALUES
  ('C1', 'Home'), ('C1', 'Work'), ('C2', 'Home')
) t(customer_id, label);

CREATE TABLE lines AS SELECT * FROM (VALUES
  (1, 'A', 2, 9.99), (2, 'B', -1, 5.00), (3, 'C', 3, NULL),
  (4, 'D', 500, 1.00), (5, 'E', 1, 4.50)
) t(line_id, sku, qty, price);

CREATE TABLE releases AS SELECT * FROM (VALUES
  ('1.9.0'), ('1.10.2'), ('1.2.11')
) t(version);

1. Keep the latest record per customer

Prompt: customers_raw has several rows per customer_id. Return one row per customer, the most recently updated.

SELECT customer_id, email, updated_at, record_id
FROM customers_raw
QUALIFY row_number() OVER (
  PARTITION BY customer_id
  ORDER BY updated_at DESC NULLS LAST, record_id DESC
) = 1
ORDER BY customer_id;

Why this answer works: C2 has two identical timestamps, so without record_id DESC the database may return either row, and a rerun can change the result. NULLS LAST states that an unknown update time never beats a known one. QUALIFY filters on the window function directly, which DuckDB supports (DuckDB QUALIFY documentation); in a database without it, wrap the query in a subquery and filter WHERE rn = 1.

Follow-up you should expect: "Why not GROUP BY customer_id with max(updated_at)?" Because max() on each column can combine values from different rows, producing a record that never existed. The free exercise pick one surviving record per account practices choosing whole rows with a rule.

2. Convert messy text amounts to numbers

Prompt: payments_raw.amount_text contains values like ' 1,250.00 ' (with spaces), '$40', '(15.00)', 'n/a' and blanks. Produce a numeric column and explain what happens to bad values.

WITH prepared AS (
  SELECT payment_id, amount_text,
         NULLIF(trim(amount_text), '') AS t
  FROM payments_raw
)
SELECT payment_id, amount_text,
       TRY_CAST(
         replace(replace(
           CASE WHEN t LIKE '(%)' THEN '-' || trim(t, '()') ELSE t END,
         ',', ''), '$', '')
       AS DECIMAL(12,2)) AS amount,
       CASE WHEN t IS NULL THEN 'missing'
            WHEN amount IS NULL THEN 'unparseable'
       END AS reason
FROM prepared
ORDER BY payment_id;

Why this answer works: TRY_CAST returns NULL instead of failing the whole query (DuckDB casting documentation), but a bare NULL hides the difference between "the source was blank" and "the source was garbage." The reason column keeps that difference. Accounting-style parentheses become a minus sign, and DECIMAL avoids floating-point rounding on money. Note that referring to the amount alias inside the same SELECT is a DuckDB convenience; in other databases, compute amount in a CTE first.

The lesson on TRY_CAST for quarantine covers this pattern step by step.

3. Standardize a category column with a mapping table

Prompt: tickets.priority_raw contains 'High', 'HIGH ' (trailing space), 'hi', Low, urgent!! and NULL. Map it to high, medium or low.

SELECT t.ticket_id, t.priority_raw, m.priority,
       CASE WHEN t.priority_raw IS NULL THEN 'missing'
            WHEN m.priority IS NULL THEN 'unmapped' END AS reason
FROM tickets t
LEFT JOIN priority_map m
  ON lower(trim(t.priority_raw)) = m.variant
ORDER BY t.ticket_id;

Why this answer works: The mapping lives in a table (variant, priority) that a stakeholder can review, rather than in a long CASE buried in the query. The LEFT JOIN keeps every ticket, and urgent!! is reported as unmapped instead of being silently forced into high. A likely follow-up is "would you map urgent to high?" The right answer is "only if the owner approves it; until then it goes on the unmapped list."

The exercise map categories with an approved dictionary uses the same pattern.

4. Find orders with no matching customer

Prompt: Return orders whose customer_id does not exist in customers, with a reason.

SELECT o.order_id, o.customer_id,
       CASE WHEN o.customer_id IS NULL THEN 'missing key'
            ELSE 'no matching customer' END AS reason
FROM orders o
ANTI JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_id;

Why this answer works: ANTI JOIN returns left rows with no match. In SQL, NULL = NULL is not true, so order 104 never matches and appears here, which is the correct outcome: it has no customer. The two reasons matter because they have different fixes: a missing key is an extraction problem, an unknown key is a reference-data problem. In databases without ANTI JOIN, use LEFT JOIN ... WHERE c.customer_id IS NULL or NOT EXISTS, and avoid NOT IN with a subquery that can contain NULLs, because it returns no rows at all.

The free exercise referential integrity with explicit exceptions builds the full exceptions report.

5. Parse dates written in different formats

Prompt: signups.signup_text mixes ISO dates and dd/mm/yyyy or mm/dd/yyyy depending on country. Produce a DATE column.

SELECT user_id, signup_text, country,
       COALESCE(
         TRY_CAST(signup_text AS DATE),
         CASE country
           WHEN 'US' THEN TRY_STRPTIME(signup_text, '%m/%d/%Y')
           WHEN 'GB' THEN TRY_STRPTIME(signup_text, '%d/%m/%Y')
         END::DATE
       ) AS signup_date
FROM signups
ORDER BY user_id;

Why this answer works: The same text, 04/03/2026, is 3 April for a US source and 4 March for a UK source, so the format must come from a declared rule, not from guessing per value. Month 13 and soon fail to parse and stay NULL; a complete answer adds a reason column as in question 2 and reports the parse rate per country. Try it on the free exercise parse dates by declared locale.

6. Check a join for fan-out before trusting it

Prompt: After joining orders to addresses, revenue went up. Show why.

SELECT
  (SELECT count(*) FROM orders) AS rows_before,
  (SELECT count(*) FROM orders o
     LEFT JOIN addresses a USING (customer_id)) AS rows_after,
  (SELECT count(*) FROM (
     SELECT customer_id FROM addresses
     GROUP BY customer_id HAVING count(*) > 1)) AS repeated_keys;

Why this answer works: Customer C1 has a home and a work address, so their order appears twice after the join and its amount is counted twice. The fix depends on the business rule: pick one address per customer first (a survivor rule), or aggregate orders before joining. Stating the expected cardinality ("many orders to one customer") before writing the join is what interviewers want to hear. The guide on checking joins before you trust them covers this in depth.

7. Quarantine bad rows with a reason and reconcile

Prompt: Order lines must have a price and a quantity between 1 and 100. Split the table into clean rows and exceptions without losing any row.

CREATE TABLE checked AS
SELECT *,
  CASE
    WHEN price IS NULL THEN 'price missing'
    WHEN qty IS NULL THEN 'quantity missing'
    WHEN qty <= 0 THEN 'quantity not positive'
    WHEN qty > 100 THEN 'quantity above 100'
  END AS reason
FROM lines;

SELECT count(*) AS rows_in,
       count(*) FILTER (WHERE reason IS NULL) AS clean,
       count(*) FILTER (WHERE reason IS NOT NULL) AS quarantined
FROM checked;

SELECT line_id, sku, qty, price, reason
FROM checked
WHERE reason IS NOT NULL
ORDER BY line_id;

Why this answer works: One reason column, with rules evaluated in a stated order, means each bad row gets exactly one explanation, and the reconciliation (5 = 2 + 3) proves nothing disappeared. CASE returns the first matching branch, so the order of the rules is the priority of the reasons. The explicit qty IS NULL branch matters: without it, a missing quantity fails both range tests and would be counted as clean. Practice the pattern with validate quantities and prices against ranges.

8. Find the highest version number

Prompt: releases.version holds 1.9.0, 1.10.2 and 1.2.11. Return the latest version.

SELECT max(version) AS text_max FROM releases;  -- text comparison

SELECT version
FROM releases
ORDER BY string_split(version, '.')::INTEGER[] DESC
LIMIT 1;

Why this answer works: Text sorts character by character, so '1.9.0' beats '1.10.2' because 9 is greater than 1. Splitting on dots and casting to an integer list makes DuckDB compare each part numerically. The same bug appears with ids, invoice numbers and any number stored as text. Practice it with highest number, not highest text.

How to practice these questions

Rewrite each answer from memory on a fresh table, then change one detail (a tie, a NULL, a new format) and see whether your query still behaves. Those twists make good follow-up practice. The SQL analyst learning path orders the underlying skills.

Frequently Asked Questions

Do interviewers accept DuckDB syntax like QUALIFY?

Usually, if you say what it does and offer the portable version. Write QUALIFY when the platform supports it, then mention the subquery with WHERE rn = 1 as the equivalent for databases that lack it.

Should I use DISTINCT to remove duplicates in an interview?

Only for exact copies, and say so. DISTINCT cannot apply a survivor rule, it keeps near-duplicates, and it leaves no record of what was removed. A survivor rule needs row_number() or an equivalent.

What if I forget the exact function name?

Say what you need ("a cast that returns NULL instead of failing") and write a reasonable guess. The approach and the edge cases you name matter more than exact function names.

Related posts

• 25 Data Cleaning Interview Questions (and What Interviewers Look For)
• pandas Interview Questions on Messy Data
• QUALIFY in DuckDB: The Cleanest Way to Keep the Latest Row
• How to Find Duplicate Rows in SQL (and Decide Which One to Keep)

Sources

• DuckDB documentation, QUALIFY clause.
• DuckDB documentation, Casting and TRY_CAST.
• DuckDB documentation, Window functions.