How to answer any SQL interview question

Step 1 Understand it

Why you need it. 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?

Watch it happen

Picture it

flowchart LR
  A["1 clarify<br/>ties? NULLs? per what?"] --> B["2 look at tables<br/>grain, keys, joins"] --> C["3 plan out loud"] --> D["4 write CTEs<br/>one step each"] --> E["5 check<br/>row counts, a known total,<br/>edge cases"]

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.

The shape of it

-- 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 know

Words in lower case are the parts you fill in.

How it works, step by step

1. Question: "Which city brings in the most delivered revenue?"
2. Clarify: delivered orders only? Customers with no city — drop them or show them as unknown? Ties?
3. Grain: one row of orders is one order; customers holds the city; join on customer_id.
4. Sanity-check the data first: 10 orders, 4 customers who ordered, 1 order with no amount.
5. Plan and write: a CTE of delivered orders → join customers → group by city → sort. Check: London = Priya 275 + Lena 60 = 335.

Analogy

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.

Where the analogy breaks

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".

Worked examples

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT customer_id) AS customers_who_ordered,
       SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) AS missing_amounts
FROM orders;

WITH delivered AS (
  SELECT customer_id, amount
  FROM orders
  WHERE status = 'delivered'
)
SELECT COALESCE(c.city, 'unknown') AS city,
       SUM(d.amount) AS delivered_revenue
FROM delivered d
JOIN customers c ON c.customer_id = d.customer_id
GROUP BY COALESCE(c.city, 'unknown')
ORDER BY delivered_revenue DESC;

Must-know 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.

In other databases

• If the interviewer doesn't name a database, say "I'll write PostgreSQL" — it is the most common choice, and it means DATE_TRUNC, ::date casts and FILTER are all available.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  How to approach SQL interview questions (video)

  YouTube search, free

• Practice

  LeetCode — Top SQL 50 study plan

  free account

• Practice

  DataLemur — SQL interview questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Say the five steps from memory, with one sentence on why each matters.

   Show answer

   1. Restate the question and clarify it: "So you want delivered revenue per city, highest first?" It catches misunderstandings before you write anything, and shows you listen.
   2. Look at the tables, their grain and the data: one row of orders is one order; the city is in customers; run a quick count. Joins and sums go wrong when you don't know what one row means.
   3. Ask about the edge cases: ties, NULLs, duplicates, date ranges, what "per" means. Each answer changes the query, and asking is what interviewers grade.
   4. Plan out loud, then write it as small CTEs, one step each, named after what they hold. Easy to follow, and easy to fix one step.
   5. Check the result: a row count and one number worked out by hand ("London = 275 + 60 = 335"). It catches fan-out, missing rows and integer division before the interviewer does.

2. Writefrom a blank page, then run it

   Use the five steps on: "Which product category had the most units sold in February?" Write your clarifying questions first.

   Show answer

   Clarifying questions first:
   • Which orders count? I'll assume everything except cancelled (shipped and pending were still sold), and say so.
   • "February" means February 2024, by order_date.
   • Units means the quantity in order_items, not the number of orders.
   • What if two categories tie? I'll return both, rather than pick one.

   Grain: one order_items row is one product in one order; the category is in products; the date and status are in orders.

   Plan: February's item lines with their category → units per category → keep the maximum. Check by hand: February has orders 105 (3 headphones, 1 backpack) and 107 (2 backpacks); 106 was cancelled and has no items. So Electronics = 3 and Bags = 1 + 2 = 3: a tie, and both come back.

   sqlCopy

   WITH feb_items AS (
     SELECT p.category, oi.quantity
     FROM orders o
     JOIN order_items oi ON oi.order_id = o.order_id
     JOIN products p     ON p.product_id = oi.product_id
     WHERE o.order_date >= '2024-02-01' AND o.order_date < '2024-03-01'
       AND o.status <> 'cancelled'
   ),
   by_category AS (
     SELECT category, SUM(quantity) AS units
     FROM feb_items
     GROUP BY category
   )
   SELECT category, units
   FROM by_category
   WHERE units = (SELECT MAX(units) FROM by_category)
   ORDER BY category;

   Try it

   2 rows

   category	units
   Bags	3
   Electronics	3

3. Predictsay it before you run it

   Before running the city query, predict its result if cancelled orders were included too.

   Show answer

   Exactly the same: London 335, Leeds 275.

   The only cancelled order is 106 (Lena, London), and its amount is NULL. SUM skips NULLs, so adding it changes nothing. A good prediction checks the data, not just the logic: "include cancelled orders" sounds like a big change, but here it adds one row with nothing to add.

   sqlCopy

   WITH delivered AS (
     SELECT customer_id, amount
     FROM orders
     WHERE status IN ('delivered', 'cancelled')
   )
   SELECT COALESCE(c.city, 'unknown') AS city,
          SUM(d.amount) AS delivered_revenue
   FROM delivered d
   JOIN customers c ON c.customer_id = d.customer_id
   GROUP BY COALESCE(c.city, 'unknown')
   ORDER BY delivered_revenue DESC;

   Try it

   2 rows

   city	delivered_revenue
   London	335
   Leeds	275

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   List three edge cases you would raise for "What is the average order value per customer?" and how each changes the query.

   Show answer

   1. Orders with no amount, or cancelled orders. Order 106 has a NULL amount. AVG skips it (Lena's average is 60, from one order); treating it as £0 gives 30. Ask which is meant, then either filter WHERE status <> 'cancelled', or use AVG(COALESCE(amount, 0)).

   2. Customers with no orders. Omar has none. Starting from orders, he simply isn't there; if the question means "per customer, all of them", start from customers with a LEFT JOIN, and decide whether he shows NULL or 0.

   3. Duplicate customers. Tom signed up twice (ids 2 and 6). Grouping by customer_id gives two Toms, one with no orders. If they are one person, group by the email instead (after cleaning it).

   (A fourth worth asking: does "average order value per customer" mean each customer's own average, or one overall figure, which is total revenue ÷ number of orders?)

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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, 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.

Follow-up questions

Tick each check when you can do it without notes