How to answer any SQL interview question
A repeatable method: restate the question, look at the tables and their grain, ask about edge cases, and plan the steps out loud. Then write the query as small CTEs, and check the result against a few rows you can work out by hand.
Why it matters
Under pressure, people jump straight into typing, write one giant query, and miss the NULLs or ties the interviewer planted. Interviewers grade the process as much as the answer: do you clarify, structure, and verify?
The idea
Step 1, clarify: say the question back in your own words, and ask about ties, NULLs, duplicates and what "per" means.
Step 2, look at the data: what is one row of each table (the grain), what are the keys, how do they join?
Step 3, plan out loud: "First I'll get delivered orders, then total them per customer, then join the city."
Step 4, write it as CTEs, one step each, running each one if you can.
Step 5, check: does the row count make sense? Does a total match a number you can work out by hand? Then say the edge cases and how your query handles them.
A pilot's checklist. Experienced pilots still run it every flight — not because they forget how to fly, but because the checklist catches the one thing that pressure makes you skip.
A checklist is the same every flight. Interviews vary: if the question is simple, go through the steps quickly; don't spend five minutes clarifying "count the rows".
Syntax
-- 1 clarify 2 look at the grain 3 plan out loud
WITH step_one AS (...), -- 4 one CTE per step
step_two AS (...)
SELECT ... FROM step_two; -- 5 check against rows you knowHow it works
- Question: "Which city brings in the most delivered revenue?"
- Clarify: delivered orders only? Customers with no city — drop them or show them as unknown? Ties?
- Grain: one row of orders is one order; customers holds the city; join on customer_id.
- Sanity-check the data first: 10 orders, 4 customers who ordered, 1 order with no amount.
- Plan and write: a CTE of delivered orders → join customers → group by city → sort. Check: London = Priya 275 + Lena 60 = 335.
Key points
- Clarify first: ties, NULLs, duplicates, date ranges, and what "per" means.
- Know each table's grain and keys before joining.
- Think out loud — the interviewer is grading your reasoning.
- Write CTEs, one step each, named after what they hold.
- Verify: a row count, one total worked out by hand, and the edge cases said out loud.
Common mistakes
- Typing straight away and writing one huge nested query.
- Not asking about ties, then using LIMIT 1 when two rows tie.
- Stopping when the query runs, without checking that the answer is right.
Not true: Once the query runs without an error, the interview answer is finished.
In fact: A query can run and still be wrong. Check it against a small example and against the edge cases (ties, NULLs, no rows) before you stop.
In the interview
I start by restating the question and asking about edge cases — ties, NULLs, duplicates, the time window. Then I look at the tables, to understand the grain and how they join. I explain my plan in steps, and write it as a chain of CTEs, so each step is readable and testable. Finally I sanity-check the output — row counts and one number I can verify by hand — and walk through how the query handles the edge cases.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.