SQL Interview Playbook

A SQL interview round is a short test. You get a question and a few tables. The person who watches you is the interviewer.

The interviewer checks two things. Is your answer right? Can you explain how you got it?

This chapter teaches a method. It does not teach new SQL. It uses what you learned in chapters 1 to 5.

The method has five steps: clarify, contract, build, check, explain. The next section shows each one.

The examples use the same shop as chapters 1 to 3: customers, orders and order_items. Question 1, “Who are our top 3 customers?”, runs through the chapter. Question 2, “What is each customer’s latest order?”, appears once, in “Compare two queries”.

Use the same five steps for every question. Say each step out loud when you do it. Then the interviewer can follow your thinking.

This table shows what you do and what you might say in each step:

A tie is when two rows have the same value, such as two customers with the same total.

A silent pause looks like no thinking. A spoken step shows your thinking, even when the query is not finished.

The next sections follow Question 1, “Who are our top 3 customers?”, through all five steps.

The question is short on purpose. “Top 3 customers” hides several choices. Say the question in your own words, and name each choice.

Some words are unclear in almost every question. This table shows five of them, and what each one changes in SQL:

See why this matters. This query counts the paid orders and adds up the paid amounts for each customer:

SELECT c.name,
       COUNT(*)      AS paid_orders,
       SUM(o.amount) AS paid_revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.status = 'paid'
GROUP BY c.name
ORDER BY c.name;

By number of orders, Asha is first. By revenue, Dev is first.

The question did not say which. A guess may be wrong.

Ask your questions before you write any SQL. A late answer can mean you start again.

Here are six questions to ask before you write SQL:

1. Grain: what does one row of the answer mean? One row per customer, or per order?
2. Rows: which rows count? Only paid orders? Do refunded orders count?
3. NULL: what should happen to a missing value?
4. Ties: what if two customers have the same total?
5. Dates: which date column? Which start and end date?
6. Output: which columns, in which order, and how many rows?

For Question 1, the talk might go like this:

• You: “Does top mean most revenue? Do refunded orders count? What if two customers tie?”
• Interviewer: “Most revenue. Paid orders only. Break ties by name.”

The clarify step is now done. You know what to write.

Sometimes the interviewer says, “You decide.” Then pick a simple default. Say it out loud: “I will assume paid orders only.”

An edge case is a row that acts in a surprising way. Look for edge cases in the data before you write the query.

This query labels every customer’s orders. A LEFT JOIN keeps Omar, who has no orders:

SELECT c.name, c.country,
       o.order_id, o.status, o.amount,
       CASE
         WHEN o.order_id IS NULL    THEN 'no orders'
         WHEN o.status <> 'paid'    THEN 'not paid'
         WHEN c.country IS NULL     THEN 'country is NULL'
         ELSE 'normal'
       END AS edge_case
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

This small shop has four edge cases:

• Dev has a NULL country. A GROUP BY country puts Dev in one group with a blank country.
• Order 103 is refunded and order 105 is pending. Should they count as revenue?
• Order 105 has a NULL amount. SUM skips it.
• Omar has no orders. An inner join drops him. A LEFT JOIN keeps him.

You do not need to solve every edge case. Name the ones that matter, and say the default you choose. This table shows common edge cases and a default for each:

Chapter 1 defined grain: what one row means. Say the grain of every table before you join them.

This table gives the grain of the three shop tables:

Test a grain with a count. This query counts the rows and the different orders in order_items:

SELECT COUNT(*)                 AS item_rows,
       COUNT(DISTINCT order_id) AS orders
FROM order_items;

What goes wrong in a join

Now a question: “What is the total of the paid orders?” Suppose you join order_items, because you want product names later. This query joins it, then adds up the amounts:

SELECT SUM(o.amount) AS paid_total
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.order_id
WHERE o.status = 'paid';

The right total is 160.50, which is 40 + 25 + 15.50 + 80. The query gives 280.50.

Four paid orders go into the join, and six rows come out. Orders 101 and 106 appear twice, so 40 and 80 are added twice.

Chapter 2 called this fan-out. The query shows no error. The question never needed items, so the fix is to not join order_items.

A contract is a written promise about the result. Write it before the SQL, and say it out loud. This is the contract for Question 1:

• Columns: name, paid_orders, paid_revenue.
• One row per: customer.
• Rows that count: paid orders only.
• Order: paid_revenue from high to low, then name.
• Size: the first 3 rows.

The “then name” part matters when two rows have the same total. This query lists all five customers, including the ones with no paid orders. Question 1 does not need that, but it shows a tie:

SELECT c.name,
       COUNT(o.order_id)              AS paid_orders,
       COALESCE(SUM(o.amount), 0.00)  AS paid_revenue
FROM customers AS c
LEFT JOIN orders AS o
       ON o.customer_id = c.customer_id
      AND o.status = 'paid'
GROUP BY c.customer_id, c.name
ORDER BY paid_revenue DESC, c.name;

COUNT(o.order_id) skips NULL, so Ben and Omar get 0. COALESCE turns their NULL sum into 0.00. Without the second ORDER BY column, the order of tied rows is not fixed.

For the top 3, no tie matters. A customer with no paid order cannot be in the top 3, so the final query can use a plain JOIN.

If the question asked for the top 5, you would need the LEFT JOIN above. Ben and Omar would tie at 0.00, and name would decide.

Do not write the whole query at once. Write one step, run it, and check the rows. Then add the next step.

A CTE gives each step a name. A CTE is a small result that you name with WITH, as in chapter 4. Step 1 keeps the paid orders:

WITH paid AS (
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE status = 'paid'
)
SELECT * FROM paid
ORDER BY order_id;

Step 2 turns the paid orders into one row per customer. The query now ends with a SELECT on the new step:

WITH paid AS (
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE status = 'paid'
),
revenue AS (
  SELECT customer_id,
         COUNT(*)    AS paid_orders,
         SUM(amount) AS paid_revenue
  FROM paid
  GROUP BY customer_id
)
SELECT * FROM revenue
ORDER BY customer_id;

Step 3 adds the names and keeps the first 3 rows. The join is safe: customers has one row per customer, so no row repeats.

WITH paid AS (
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE status = 'paid'
),
revenue AS (
  SELECT customer_id,
         COUNT(*)    AS paid_orders,
         SUM(amount) AS paid_revenue
  FROM paid
  GROUP BY customer_id
)
SELECT c.name, r.paid_orders, r.paid_revenue
FROM revenue AS r
JOIN customers AS c ON c.customer_id = r.customer_id
ORDER BY r.paid_revenue DESC, c.name
LIMIT 3;

Each step adds one idea, and each result was checked before the next step. If a number is wrong later, you know which step to open.

A result that looks right can still be wrong. After the query runs, do three checks:

1. Is the number of rows what you guessed?
2. Do the parts add up to the whole table?
3. Does one row match the data when you work it out by hand?

Check 2 is easy to run on the revenue step, before LIMIT. This query adds up the per-customer rows, then compares them with the paid orders in the table:

WITH revenue AS (
  SELECT customer_id,
         COUNT(*)    AS paid_orders,
         SUM(amount) AS paid_revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY customer_id
)
SELECT SUM(paid_orders)  AS grouped_orders,
       SUM(paid_revenue) AS grouped_revenue,
       (SELECT COUNT(*)
        FROM orders WHERE status = 'paid')    AS table_orders,
       (SELECT SUM(amount)
        FROM orders WHERE status = 'paid')    AS table_revenue
FROM revenue;

The pairs match, so the grouping step lost no rows and repeated none. Say it out loud: “The grouped total equals the table total.”

Run the same check again after you join, to catch a join that repeats rows.

Check 3: Asha has orders 101 and 102. 40.00 + 25.00 = 65.00. The row for Asha says 65.00, so it matches.

When a check fails

Do not read the final SELECT again and again. Count the rows after each step. This query compares the paid orders with the rows after a join to order_items:

SELECT (SELECT COUNT(*)
        FROM orders
        WHERE status = 'paid') AS paid_orders,
       (SELECT COUNT(*)
        FROM orders AS o
        JOIN order_items AS i ON i.order_id = o.order_id
        WHERE o.status = 'paid') AS after_join;

The first step where the count is not what you guessed has the bug. Change one thing at a time, then count again.

Many questions have more than one right query. A strong answer says why you chose one. Take Question 2: “What is each customer’s latest order?”

First, ask: does a pending order count? Say yes for now. Option A uses ROW_NUMBER from chapter 5:

WITH ranked AS (
  SELECT customer_id, order_id, order_date,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC, order_id DESC
         ) AS rn
  FROM orders
)
SELECT customer_id, order_id, order_date
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

If pending orders should not count, add WHERE status <> 'pending' inside the ranked step. Then Carla’s latest order is 104.

Option B uses a subquery that finds the newest date for each customer. A subquery is a query inside a query, as in chapter 4:

SELECT o.customer_id, o.order_id, o.order_date
FROM orders AS o
WHERE o.order_date = (
  SELECT MAX(x.order_date)
  FROM orders AS x
  WHERE x.customer_id = o.customer_id
)
ORDER BY o.customer_id;

The two options differ only on a tie. Suppose a customer had two orders on the same newest date. This table compares the options:

To explain a choice, say three things:

• The options you compared.
• What you chose.
• When you would choose the other option.

When the query works and the check passes, explain it. Do not read the code word by word. The interviewer can read it.

Here is the Step 3 query again, with the contract written at the top as comments:

-- One row per customer. Paid orders only.
-- Sorted by revenue, then name. First 3 rows.
WITH paid AS (
  SELECT order_id, customer_id, amount
  FROM orders
  WHERE status = 'paid'
),
revenue AS (
  SELECT customer_id,
         COUNT(*)    AS paid_orders,
         SUM(amount) AS paid_revenue
  FROM paid
  GROUP BY customer_id
)
SELECT c.name, r.paid_orders, r.paid_revenue
FROM revenue AS r
JOIN customers AS c ON c.customer_id = r.customer_id
ORDER BY r.paid_revenue DESC, c.name
LIMIT 3;

Explain each step by what its rows mean. This table shows what you might say for each part:

Each exercise opens in the SQL workspace with its own tables. Run the five steps on each one: clarify, contract, build, check, explain.

The first six labs use chapters 1 to 5 and chapter 13. The last six are optional. Each one names the chapter to read first.