← SQL Foundations for Data Engineering

SQL0 Interview questions

Read the answers out loud. The quiz tests whether you know it; this tests whether you can say it under pressure with someone watching. Those are different skills, and only one of them gets you the offer.

Every answer here is written to be spoken in under 90 seconds. If you find yourself going longer, you've drifted into reciting.

---

Warm-up — these are definitely coming

Q. What's the difference between WHERE and HAVING?

WHERE filters rows before grouping, HAVING filters groups after aggregation. So WHERE can't see aggregate functions — the grouping hasn't happened yet. Practically, I put row conditions in WHERE and group conditions in HAVING, because filtering early means you aggregate less data. If I see a row-level condition in HAVING, that's usually someone who didn't know the difference.

Q. COUNT(*) versus COUNT(column)?

COUNT(*) counts rows. COUNT(column) counts rows where that column isn't null. The place it bites is after a LEFT JOIN — if you count star, a parent row with no children comes back as one, because the row still exists, it's just full of nulls. Count a column from the right-hand table and you correctly get zero.

Q. What's the difference between INNER and LEFT JOIN?

Inner keeps only matching rows. Left keeps everything from the left side and fills nulls where there's no match. The subtlety people miss is that if you then put a condition on the right-hand table in your WHERE clause, you've silently turned it back into an inner join — because unmatched rows have null there, and null compared to anything is unknown, so WHERE drops them. If you want to filter the right side and keep the left, the condition goes in the ON clause.

Q. UNION versus UNION ALL?

UNION removes duplicates, UNION ALL doesn't. UNION ALL is cheaper because deduplicating means sorting or hashing everything. I default to UNION ALL and only use UNION when I actually want duplicates gone — and if I do, I usually want to know why they're there in the first place.

Q. How do you find duplicates in a table?

Group by whatever should be the key, count, and having count greater than one. That's the check I run before trusting any join, and it takes about ten seconds.

---

Depth — where a senior interviewer probes

Q. SELECT COUNT(*) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM blocked) returns zero, but there are four million orders. What happened?

There's a null in the subquery result. NOT IN expands to a chain of not-equals joined with AND, and comparing anything to null gives unknown rather than false. true AND unknown is unknown, and WHERE only keeps rows where the condition is actually true — so every row gets dropped. The fix I reach for is NOT EXISTS, because it's correct by construction rather than by remembering, and it keeps working the day someone makes that column nullable.

The follow-up they ask next: "Why doesn't plain IN have the same problem?"

Because IN is an OR chain instead of an AND chain, and true OR unknown is true. So a matching row still comes back. That asymmetry is exactly why this bug survives testing — people test the positive case, IN works fine, and nobody tries the negation.

---

Q. You add a join to a customer dimension and revenue doubles. Walk me through it.

That's fan-out. A join isn't a lookup, it's a filtered cross product, so if the dimension has two rows for a customer, every order for that customer now appears twice and the sum double-counts. The first thing I'd run is a uniqueness check on the join key in the dimension — group by, count, having count greater than one. Exactly two times is a strong hint that every key is duplicated once, which usually means a load ran twice. Then I'd fix it upstream rather than patch the query.

The follow-up: "Suppose you can't fix it upstream today and the report is due."

Then I'd deduplicate the dimension explicitly in a CTE with ROW_NUMBER, and write down which row wins and why — most recent by updated timestamp, with a tie-breaker. What I wouldn't do is add SELECT DISTINCT, because that hides the symptom, changes the aggregate in ways I haven't reasoned about, and leaves the actual duplicate in place for the next person.

---

Q. Three clean tables — orders, shipments, returns, no duplicate keys anywhere. You join all three and sum shipment weight and refund amount. Both totals are wrong. Why?

That's a chasm trap. Orders to shipments is one-to-many, and orders to returns is independently one-to-many. Joining both to the same parent gives you the cross product of the two branches — an order with three shipments and two returns produces six rows. So shipment weight gets counted twice, once per return, and refunds get counted three times, once per shipment. Both aggregates are wrong, by different factors, in the same query. The fix is to aggregate each branch in its own subquery first so each collapses to one row per order, then join those results.

The follow-up: "How would you have caught it before shipping?"

Count the rows before and after the join. For a many-to-one join the count shouldn't grow. If it grows, something fanned out. That's a two-second check and I do it every time.

---

Q. Write "the latest row per customer". Then tell me what's wrong with it.

ROW_NUMBER() over partition by customer, order by updated-at descending, wrapped in a subquery, filtered to row number equals one. What's wrong with the naive version is the ordering — if two rows share the same timestamp, which one wins is undefined, and the engine can pick a different one on each run depending on the plan and parallelism. So you get a pipeline that produces different output from identical input, and it doesn't reproduce when you go looking. You need a tie-breaker: a sequence number, an event id, a file offset — something monotonic.

The follow-up: "Could you use RANK() instead of ROW_NUMBER()?"

Not for deduplication. RANK() gives tied rows the same rank, so a tie at the top gives you two rows back with rank one and your deduplicated table still has duplicates. That bug only appears when real data contains a tie, so it passes every test you wrote. RANK() is right for "top three including ties" — which is a different question, and one I'd ask the stakeholder to clarify.

---

Q. Why can't you put a window function in the WHERE clause?

Because of the logical order of evaluation. WHERE runs before window functions do, so the row number doesn't exist yet at that point. You wrap the query in a subquery or CTE and filter on the outside. Some engines have a QUALIFY clause that lets you skip the wrapper, but it isn't universal, so I'd check before using it in code that has to be portable.

---

Q. Does Redshift enforce primary keys?

No — but that's only half the answer. The documentation says constraints are permitted but informational only, not enforced by the system, and used by the query planner. So if you declare a primary key on a column that isn't actually unique, you've told the optimiser something false, and it's entitled to act on it — it might eliminate a deduplication step or simplify a join. Which means a false declaration can produce a wrong result, not just a slow one. So I still declare them, because they help the planner and document intent, but I enforce uniqueness in the pipeline and assert it after every load.

The follow-up: "So how do you actually enforce it?"

Dedup on load with ROW_NUMBER, and then a post-load assertion — group by the key, having count greater than one, must return zero rows — as an actual pipeline step that fails the run, not something a human remembers to check.

---

Design

Q. Design the daily load for a customer dimension from a CDC feed. Tell me about correctness.

First I'd ask two things: what's the grain — one row per customer, or history — and what's the late-arriving window, because that decides whether an incremental load can ever be correct.

Assuming current-state, one row per customer: I'd land the raw CDC events immutably, then build the dimension by deduplicating with ROW_NUMBER partitioned by customer, ordered by the source timestamp with a monotonic tie-breaker like the log sequence number. Deletes have to be handled explicitly — a delete event is the latest row and should either remove the record or set a flag, and if you ignore delete events entirely your dimension slowly fills with customers who no longer exist.

For correctness I'd assert three things as pipeline steps, not manual checks: the output is unique by customer id; the distinct customer count in the source equals the row count in the output, which catches a filter that silently dropped keys; and a row count delta against yesterday within an expected band, which catches an upstream partial load.

For change detection I'd use IS DISTINCT FROM rather than not-equals, because a field going from null to a value is a real change and not-equals evaluates to unknown and misses it.

And I'd want to know whether the timestamp I'm ordering on is generated by the source database or by the pipeline, because if it's the pipeline's clock, out-of-order delivery will silently pick the wrong winner.

---

Debug

Q. A dashboard number is 3% higher than finance's. Where do you start?

As an ordered checklist:

One — reproduce the number and pin the exact filters and date range. Three percent is small, so "different date boundary" or "different timezone" is genuinely likely before anything clever.

Two — count rows before and after every join in the query. If any many-to-one join increases the count, that's fan-out and I've found it.

Three — uniqueness-check every dimension key involved. That's the cause of most fan-out.

Four — check for a chasm trap: more than one independent one-to-many branch off the same parent.

Five — check the null handling. Is a NOT IN dropping rows? Is a WHERE on the right side of a left join quietly making it inner? Is a CASE with no ELSE producing nulls that an aggregate then ignores?

Six — only then compare definitions with finance, because quite often nobody is wrong and the two numbers are answering different questions.

I'd do one through four before opening a conversation, because they're fast and they're where the money usually is.

Q. A query that ran in 40 seconds now takes 12 minutes. Nothing changed in the SQL.

I'd want to know what did change, in this order: data volume, data distribution, and statistics.

Volume is the obvious one — did an upstream load double the table?

Distribution matters more than people expect. If one key became very heavy, a join or group-by will skew and one worker does all the work while the rest idle.

And stale statistics can flip a plan — the optimiser thinks a table is small, chooses a broadcast or a nested loop, and it's catastrophically wrong at the real size.

Then I'd actually read the plan rather than guess, and compare it to what it used to be if I have it. I'd also check whether the query has several COUNT(DISTINCT) calls, because those are memory-bound in cardinality and degrade badly as data grows.

I'm being deliberately engine-agnostic here — the specific tools differ between Redshift, Athena and Spark, and I'd use whichever one we're actually on.

---

The "it depends" question

Q. Should you use SELECT DISTINCT to remove duplicates?

It depends, and here's what I'd ask you: are the rows genuinely byte-identical, or do we have multiple versions of the same entity?

If they're truly identical duplicates — an accidental double insert of the same row — then DISTINCT is fine and honest.

But if it's the same customer with different timestamps, DISTINCT won't help, because the rows aren't identical. And even where it does collapse them, it can't express which version should win. That's a ROW_NUMBER job, because deduplication requires choosing, and DISTINCT doesn't choose.

The test I'd apply: if you can't tell me which row DISTINCT kept, you weren't deduplicating, you were hoping.

---

Red flags — answers that sound right and lose the offer

Practising this

Say the answers to a wall, timed. Anything over 90 seconds gets cut. Then have someone ask you the follow-ups only — those are the ones candidates aren't ready for, and they're where the differentiation actually happens.