QUERY SYSTEMS

SQL Interview Playbook

Chapter 10AdvancedCapstone

Orientation

Interviews Test Thinking, Communication, And Correctness

A SQL interview is not just a hidden unit test. It is a live review of how you turn ambiguity into a trustworthy query while someone watches your reasoning.

The best candidates slow down at the beginning, define the output contract, write SQL in inspectable layers, and then verify the result out loud. They make the interviewer confident that the query would survive production data, not just the toy rows on the screen.

This capstone turns the previous chapters into an interview operating system: clarify, contract, build, verify, explain, and defend tradeoffs.

Why data engineers care

Interviewers use SQL prompts to measure data judgment: grain, joins, edge cases, readability, and whether you can explain why a row appears.

Core mental model

Treat every prompt as a contract negotiation first and a query-writing task second.

Turn an interview prompt into a contract
Input data
orders5 rows
order_idbuyer_idstatustotal_amountcreated_at
10011paid802026-01-12 08:40
10023paid1202026-01-12 10:02
10041paid2202026-01-15 14:20
10052paid5402026-01-18 09:05
10065refunded602026-01-18 10:15
customers4 rows
customer_idcountrydeleted_at
1USNULL
2GBNULL
3NULLNULL
5US2026-02-01
WITH paid_orders AS (
  SELECT order_id, buyer_id, total_amount
  FROM orders
  WHERE status = 'paid'
    AND created_at >= '2026-01-01'
    AND created_at < '2026-02-01'
),
country_revenue AS (
  SELECT
    COALESCE(c.country, 'unknown') AS country_bucket,
    COUNT(*) AS paid_orders,
    SUM(p.total_amount) AS paid_revenue
  FROM paid_orders AS p
  JOIN customers AS c
    ON c.customer_id = p.buyer_id
  GROUP BY COALESCE(c.country, 'unknown')
)
SELECT country_bucket, paid_orders, paid_revenue
FROM country_revenue
ORDER BY paid_revenue DESC, country_bucket ASC;
Query result3 rows
country_bucketpaid_orderspaid_revenue
GB1540
US2300
unknown1120

The output contract is explicit: one row per country bucket, paid orders only, January only, deterministic ordering.

1. Clarify

ask grain, filters, timeframe

2. Contract

name columns and ordering

3. Build

write readable CTE layers

4. Verify

count rows and inspect edge cases

5. Explain

narrate tradeoffs and final answer

Strong interviews are loops, not typing contests. Each pass tightens the contract before more SQL is added.

levelbehaviorsignal
Weakstarts typing immediatelymisses grain and edge cases
Strongclarifies output and tests joinscorrect, readable answer
Seniorstates assumptions, tradeoffs, verification, and costinterviewer trusts production judgment

Common mistake

Starting to type before naming the output contract.

The query may be correct for a different question, and the interviewer cannot tell which assumptions are intentional.

Better habit

  • State grain, filters, ordering, and edge cases before writing SQL.
  • Build the query in named layers that can be inspected.
  • Narrate verification checks after the final SELECT.
What to say

“Before I write SQL, I want to make the output contract explicit so we agree on grain, filters, tie handling, and null behavior.”

Remember this

A senior SQL answer starts before the first SELECT and ends after verification.