What SQL Interviews Actually Test

SQL rounds look like puzzles and are scored like code review. Four things get marks, and only one of them is “did it return the right rows”.

What is actually being scored

1. CORRECTNESS   Does it return the right rows — including when a table is empty,
                 a join key is NULL, or a group has no matching rows?

2. READABILITY   Can the interviewer verify it by reading? CTEs with names beat
                 four levels of nested subqueries returning the same answer.

3. REASONING     Did you say what the query does before running it, and did you
                 read the result back to check it matched?

4. AWARENESS     Do you know what it costs — the join that fans out, the
                 correlated subquery that runs per row, the missing index?

Candidates lose on 2 and 3 far more often than on 1. A correct query delivered in silence, with no stated assumptions, reads as guessing that happened to work.

Set up the substrate

Everything in this track runs on DuckDB — one binary, no server, and syntax close enough to PostgreSQL that the queries transfer.

# macOS / Linux
curl -fsSL https://install.duckdb.org | sh
# or: brew install duckdb   |   winget install DuckDB.cli

duckdb --version

$ duckdb --version
v1.4.1 (Andium) 7c039464e4

Create the schema once. Every later lesson assumes these four tables.

-- interview.sql
CREATE TABLE customers (
    customer_id  INTEGER PRIMARY KEY,
    name         VARCHAR,
    country      VARCHAR,
    signup_date  DATE
);

CREATE TABLE orders (
    order_id     INTEGER PRIMARY KEY,
    customer_id  INTEGER,
    order_date   DATE,
    amount       DECIMAL(10,2),
    status       VARCHAR
);

CREATE TABLE products (
    product_id   INTEGER PRIMARY KEY,
    name         VARCHAR,
    category     VARCHAR,
    price        DECIMAL(10,2)
);

CREATE TABLE order_items (
    order_id     INTEGER,
    product_id   INTEGER,
    quantity     INTEGER
);

INSERT INTO customers VALUES
    (1, 'Ana',   'UK',     DATE '2024-01-15'),
    (2, 'Bo',    'US',     DATE '2024-02-03'),
    (3, 'Cy',    'UK',     DATE '2024-02-20'),
    (4, 'Di',    'India',  DATE '2024-03-11'),
    (5, 'Eve',   'US',     DATE '2024-06-01'),
    (6, 'Fay',   NULL,     DATE '2024-06-18');

INSERT INTO orders VALUES
    (101, 1,    DATE '2024-03-01',  120.00, 'shipped'),
    (102, 1,    DATE '2024-03-15',   80.50, 'shipped'),
    (103, 2,    DATE '2024-03-18',  310.00, 'shipped'),
    (104, 3,    DATE '2024-04-02',   45.00, 'cancelled'),
    (105, 1,    DATE '2024-04-20',  200.00, 'shipped'),
    (106, 2,    DATE '2024-05-05',   99.99, 'pending'),
    (107, 4,    DATE '2024-05-30',  150.00, 'shipped'),
    (108, NULL, DATE '2024-06-02',   75.00, 'shipped');

INSERT INTO products VALUES
    (1, 'Keyboard', 'hardware',  49.99),
    (2, 'Monitor',  'hardware', 199.99),
    (3, 'Licence',  'software',  99.00),
    (4, 'Cable',    'hardware',   9.99);

INSERT INTO order_items VALUES
    (101, 1, 2), (101, 4, 3), (102, 3, 1), (103, 2, 1),
    (103, 1, 1), (105, 2, 1), (106, 3, 2), (107, 4, 5);

duckdb interview.duckdb < interview.sql
duckdb interview.duckdb -c "SELECT count(*) AS customers FROM customers;"

$ duckdb interview.duckdb -c "SELECT count(*) AS customers FROM customers;"
┌───────────┐
│ customers │
│   int64   │
├───────────┤
│         6 │
└───────────┘

The data is small on purpose. Six customers and eight orders is enough to contain every trap in this track and small enough that you can verify a result by hand — which is exactly what you should do in an interview before claiming the query works.

Three deliberate landmines are already in there: customer 6 has a NULL country, order 108 has a NULL customer_id, and customers 5 and 6 have no orders at all. Each one breaks a query that looks correct.

Read the result, not the query

SELECT c.name, count(*) AS order_count
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY order_count DESC, c.name;

$ duckdb interview.duckdb < q1.sql
┌─────────┬─────────────┐
│  name   │ order_count │
│ varchar │    int64    │
├─────────┼─────────────┤
│ Ana     │           3 │
│ Bo      │           2 │
│ Cy      │           1 │
│ Di      │           1 │
└─────────┴─────────────┘

Four rows. There are six customers. Say that out loud — it is the single highest-value habit in a SQL round:

“Four rows back from six customers. Eve and Fay have no orders and an inner join drops them. If the question is ‘how many orders per customer’, that is probably wrong — I want a LEFT JOIN and a zero. Let me confirm which the question means.”

Here is the same question answered the other way:

SELECT c.name, count(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY order_count DESC, c.name;

┌─────────┬─────────────┐
│  name   │ order_count │
│ varchar │    int64    │
├─────────┼─────────────┤
│ Ana     │           3 │
│ Bo      │           2 │
│ Cy      │           1 │
│ Di      │           1 │
│ Eve     │           0 │
│ Fay     │           0 │
└─────────┴─────────────┘

Note count(o.order_id) and not count(*). With a LEFT JOIN, unmatched rows still produce one row with NULLs, and count(*) counts that row:

SELECT c.name, count(*) AS wrong, count(o.order_id) AS right
FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.name IN ('Eve', 'Ana')
GROUP BY c.name ORDER BY c.name;

┌─────────┬───────┬───────┐
│  name   │ wrong │ right │
│ varchar │ int64 │ int64 │
├─────────┼───────┼───────┤
│ Ana     │     3 │     3 │
│ Eve     │     1 │     0 │
└─────────┴───────┴───────┘

count(*) says Eve has one order. She has none. count(col) skips NULLs, which is exactly what a LEFT JOIN needs — and it is the most frequently missed detail in this entire subject.

The order clauses actually execute in

WRITTEN                EXECUTED
SELECT      5          FROM        1
FROM        1          WHERE       2
WHERE       2          GROUP BY    3
GROUP BY    3          HAVING      4
HAVING      4          SELECT      5
ORDER BY    6          ORDER BY    6
LIMIT       7          LIMIT       7

This is not trivia; it explains two errors you will otherwise hit blind.

SELECT country, count(*) AS n
FROM customers
WHERE n > 1
GROUP BY country;

$ duckdb interview.duckdb < bad.sql
Binder Error: Referenced column "n" not found in FROM clause!
Candidate bindings: "customers.name"

WHERE runs before SELECT, so the alias n does not exist yet. HAVING runs after GROUP BY and can see it:

SELECT country, count(*) AS n
FROM customers
GROUP BY country
HAVING count(*) > 1
ORDER BY country;

┌─────────┬───────┐
│ country │   n   │
│ varchar │ int64 │
├─────────┼───────┤
│ UK      │     2 │
│ US      │     2 │
└─────────┴───────┘

The rule that follows: WHERE filters rows, HAVING filters groups. Putting a row condition in HAVING still works but scans more; putting a group condition in WHERE is an error.

How to run the round

1. RESTATE       "So: one row per customer, including customers with no orders,
                  and cancelled orders should not count. Correct?"

2. NAME THE SHAPE "One row per customer means grouping by customer. Including
                  zero-order customers means a LEFT JOIN, driven from customers."

3. WRITE IT      Small steps. Run the CTE alone before joining it.

4. READ IT BACK  "Six rows for six customers, Eve and Fay at zero. That matches."

5. VOLUNTEER     "This scans all orders. On a real table I'd want an index on
                  orders(customer_id), and status in the join condition rather
                  than the WHERE so the LEFT JOIN is preserved."

Step 5 is what separates a mid-level answer from a senior one, and it takes ten seconds.

The clarifying questions worth asking

"Should customers with zero orders appear?"        → JOIN vs LEFT JOIN
"Do cancelled orders count?"                       → the status filter, and where it goes
"What should ties do — all of them, or one?"       → RANK vs DENSE_RANK vs ROW_NUMBER
"Is customer_id ever NULL in orders?"              → whether anti-joins are safe
"Roughly how large are these tables?"              → whether the plan matters
"Which dialect — Postgres, MySQL, something else?" → syntax you can safely use

Asking two of these costs thirty seconds and prevents the most common failure in the round: answering a different question correctly.

Practice

4 rows returned, 6 customers exist

Eve  wrong=1  right=0

Binder Error: Referenced column "n" not found in FROM clause!

Zero-order customers? Cancelled orders? Ties? NULL keys? Table size? Dialect?

Next: joins and NULL semantics — the four join types, and why NOT IN returns nothing when the subquery contains a NULL.