D8LooP - Practise Data Engineering

01 · Orientation

Interviews Test Thinking, Communication, And Correctness

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

⏱ 5 min · Topic 1 of 24

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.

Core mental model

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

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.

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

Turn an interview prompt into a contractworked example

SQL

Input data

orders5 rows

order_id	buyer_id	status	total_amount	created_at
1001	1	paid	80	2026-01-12 08:40
1002	3	paid	120	2026-01-12 10:02
1004	1	paid	220	2026-01-15 14:20
1005	2	paid	540	2026-01-18 09:05
1006	5	refunded	60	2026-01-18 10:15

customers4 rows

customer_id	country	deleted_at
1	US	NULL
2	GB	NULL
3	NULL	NULL
5	US	2026-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;

Result · 3 rows

country_bucket	paid_orders	paid_revenue
GB	1	540
US	2	300
unknown	1	120

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

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.

SQL Interview Playbook