Interview questions
SQL cleaning and debugging
mid
1. A CAST to integer fails halfway through a large query. How do you find and handle the bad values?
What it tests: Validation before casting.
Model answer and scoring
I find them with a query such as SELECT value FROM t WHERE NOT regexp_full_match(value, '-?[0-9]+'). Then I decide the rule: quarantine with a reason, or convert to NULL if the rules allow. I validate against the rule's pattern before casting, rather than letting TRY_CAST decide what is valid.
Strong answers
- Finds the values first.
- Rule-driven handling.
Red flags
- Wraps everything in TRY_CAST and ignores the NULLs.
Practise it: Validate quantities and prices against ranges
mid
2. Write the idea of a query that keeps the latest version of each order line.
What it tests: Window functions for deduplication.
Model answer and scoring
Partition by the grain and order by version descending: SELECT * FROM lines QUALIFY row_number() OVER (PARTITION BY order_id, line_id ORDER BY version DESC) = 1. Versions must be compared as numbers, so I cast version before ordering if it's stored as text. Rows with rank > 1 are the superseded versions for the exception report.
Strong answers
- Correct partition and ordering.
- Typed comparison.
Red flags
- Uses MAX(version) and loses the other columns.
Practise it: Deduplicate order lines at the right grain
junior
3. A colleague used trim(code) in DuckDB but codes still don't match. Why?
What it tests: Tool-specific whitespace behaviour.
Model answer and scoring
DuckDB's trim without arguments removes spaces only. If codes are padded with tabs or line breaks, they remain. Use trim(code, ' ' || chr(9) || chr(13) || chr(10)) with the characters the rules name.
Strong answers
- Knows the default and the fix.
Red flags
- Suggests casting to integer to remove padding.
Practise it: Clean an order-line export
senior
4. How do you produce an exception report with the first failing rule per row in SQL?
What it tests: Ordered validation logic.
Model answer and scoring
One CASE expression with the rules as WHEN branches in the declared order; CASE returns the first match. I compute it in a CTE, and derive both outputs from it: clean rows where the reason is NULL, exceptions where it isn't. That keeps both outputs consistent and every input row accounted for.
Strong answers
- Single CASE in order.
- Both outputs from one CTE.
Red flags
- Separate unrelated queries per rule that double-count rows.
Practise it: Validate quantities and prices against ranges
junior
5. How do you find rows whose amount text can't be converted to a decimal in DuckDB?
What it tests: Safe casting.
Model answer and scoring
SELECT * FROM t WHERE amount IS NOT NULL AND TRY_CAST(amount AS DECIMAL(12,2)) IS NULL.
TRY_CAST returns NULL on failure, so I can quarantine those rows with a reason instead of the query failing.
Strong answers
- Uses TRY_CAST.
- Separates missing from unparseable.
Red flags
- Uses CAST and lets the query fail.
Practise it: Clean an order-line export
mid
6. Write the idea of a query that keeps one row per (order_id, line_id) with the highest version.
What it tests: Window functions.
Model answer and scoring
SELECT * FROM lines QUALIFY ROW_NUMBER() OVER (PARTITION BY order_id, line_id ORDER BY CAST(version AS INTEGER) DESC, ingest_id) = 1.
The cast makes 10 beat 9, and ingest_id makes ties deterministic.
Strong answers
- Partition by the grain.
- Numeric order and tie-break.
Red flags
- Uses GROUP BY with MAX on every column.
Practise it: Deduplicate order lines at the right grain
mid
7. How do you produce a clean table and an exceptions table from the same SQL logic?
What it tests: Single source of rules.
Model answer and scoring
I compute one reason column with a CASE in the declared rule order in a CTE. Clean rows are WHERE reason IS NULL; exceptions are the rest with their reason.
That guarantees every input row lands in exactly one output.
Strong answers
- One CASE in rule order.
- Partition into clean and exceptions.
Red flags
- Writes separate, inconsistent filters.
Practise it: Validate quantities and prices against ranges
junior
8. A query from Stack Overflow uses TOP 10 and fails in DuckDB. Why?
What it tests: Dialect awareness.
Model answer and scoring
TOP is SQL Server syntax. DuckDB uses LIMIT, and for per-group top rows a window function with QUALIFY.
I check the declared dialect before copying syntax.
Strong answers
- Knows LIMIT/QUALIFY.
Red flags
- Assumes all SQL is the same.
senior
9. Why can NOT IN (subquery) return no rows when the subquery contains a NULL?
What it tests: Three-valued logic.
Model answer and scoring
x NOT IN (1, NULL) means x <> 1 AND x <> NULL; the second comparison is unknown, so the whole condition is never true.
I use NOT EXISTS or filter NULLs out of the subquery.
Strong answers
- Explains three-valued logic.
- Uses NOT EXISTS.
Red flags
- Can't explain the empty result.
mid
10. How do you test a SQL cleaning query?
What it tests: Validation queries.
Model answer and scoring
I write checks: row counts in equals clean plus exceptions, the clean key is unique, required columns have no NULLs, and values fall in allowed ranges.
I run them on the visible data and on edge cases such as empty strings, padding and duplicates.
Strong answers
- Reconciliation and invariants.
- Edge cases.
Red flags
- Eyeballs the first rows.
Model answers are original study material for practice. They are not employer-endorsed and don't guarantee an outcome.