Four correct-looking queries, one requirement. Which do you ship? These are the questions an editor cannot ask — every option runs, and the difference is correctness under NULLs and duplicates, what the optimizer can do with the shape, and whether anyone can review it six months later.
Semi-joins, anti-joins and OR chains — where the natural phrasing costs the most.
Aggregation shapes
4
Top-N, dedup, ratios: the same numbers reached four different ways.
Structure & readability
4
CTEs, recursion and repeated expressions — how the query reads to the next person.
Writing changes, not just reads
4
Upserts, updates, deletes and transaction boundaries. Where a wrong choice is not recoverable.
Time & change
4
Ranges, gaps, as-of joins and incremental predicates.
Evergreen · asked verbatim
6
The flat form, in the words interviewers actually use. Same ground as the questions above, asked as recall rather than as a situation — because the two fail separately, and a candidate who can debug a fan-out can still stall on “WHERE versus HAVING”.
01 / 26
Joins & set opsGrain & fan-out
"Customers who have placed at least one order." You can write it with EXISTS, with IN, or with a JOIN. Which do you ship?
Why they ask this
All three run and two of them are right. The JOIN version is the one people reach for and the only one that can silently change the row count.
Say this
EXISTS. It states "at least one" exactly, it stops at the first match, and it cannot duplicate the left row. A plain JOIN returns one row per matching order, so a customer with three orders appears three times.
The reasoning
The requirement is a semi-join: filter the left side by the existence of a match, without bringing anything back from the right. EXISTS is the only one of the three that means precisely that. `IN` means the same thing when the subquery column is not nullable, and modern optimizers execute both identically — the difference is that IN invites you to select a nullable column and inherit the NULL problem.
The JOIN is the interesting failure. It is not wrong so much as it answers a different question: it produces the customer once per order. If the next person adds `SUM(c.lifetime_value)` to that query, the value is now multiplied by the order count, and nothing errors. Bolting `DISTINCT` on afterwards hides the duplication but also collapses genuinely distinct rows and costs a full sort.
The one time to prefer the JOIN is when you actually need columns from the right side. At that point it is no longer a semi-join, and you should say so — the requirement changed.
The formulations
EXISTSship
SELECT c.*
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
States "at least one" directly, cannot fan out, and short-circuits on the first match.
INworks
SELECT c.*
FROM customers c
WHERE c.id IN (SELECT o.customer_id FROM orders o);
Equivalent here and usually the same plan — but inherits NULL semantics if the inner column is nullable.
JOINavoid
SELECT c.*
FROM customers c
JOIN orders o ON o.customer_id = c.id;
Returns one row per order, not per customer. The grain silently changed.
JOIN + DISTINCTavoid
SELECT DISTINCT c.*
FROM customers c
JOIN orders o ON o.customer_id = c.id;
Repairs the row count by sorting the whole result, and hides that the shape was wrong.
See it verified against SQLite
One customer, three orders. Only the JOIN changes the count.
Given these rows
customers
id
1
2
orders
customer_id
1
1
1
The query
SELECT 'EXISTS' AS form, COUNT(*) AS rows_out FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)
UNION ALL
SELECT 'JOIN', COUNT(*) FROM customers c JOIN orders o ON o.customer_id = c.id;
Returns
form
rows_out
EXISTS
1
JOIN
3
The answer most people give
"JOIN is faster because subqueries are slow." Correlated subqueries have not been executed row-by-row by a serious optimizer in twenty years — EXISTS is recognised and planned as a semi-join. Choosing the JOIN for speed buys nothing and costs the grain.
They’ll ask next
Now I also want each customer's most recent order date. Does your answer change?
A filter is "status = shipped OR priority = high". Would you ever write that as two queries UNION ALL'd together instead?
Why they ask this
It sounds like a step backwards. It is a real technique with a real reason, and knowing when it applies shows you understand how an index gets chosen.
Say this
Sometimes, yes — an OR across two different columns often prevents the optimizer from using either index, while two UNION ALL'd branches can use one index each. But you have to handle the rows matching both conditions, or you will double-count them.
The reasoning
The problem with OR is that a single index scan cannot satisfy both sides. Engines can sometimes bridge this with a bitmap or index-merge plan, but many fall back to a full scan. Splitting into two branches lets each branch use the index that suits it, and the engine concatenates the results.
The catch is duplicates. A row that is both shipped and high priority appears in both branches, so `UNION ALL` double-counts it. `UNION` deduplicates and costs a sort over the whole result, which often gives back the win. The precise version adds `AND NOT (first condition)` to the second branch, so the branches are disjoint by construction.
Do this only when a plan shows the OR is causing a scan and the table is large enough to care. Written speculatively it is three times the SQL for no gain, and the disjointness condition is one more thing to get wrong. Measure, then rewrite.
The formulations
Plain ORship
SELECT * FROM orders
WHERE status = 'shipped' OR priority = 'high';
Start here. It is what you mean, and on most data the plan is fine.
UNION ALL with a disjointness guardworks
SELECT * FROM orders WHERE status = 'shipped'
UNION ALL
SELECT * FROM orders WHERE priority = 'high' AND status <> 'shipped';
Each branch can use its own index and the branches cannot overlap. Reach for it only after a plan says so.
UNION ALL without the guardavoid
SELECT * FROM orders WHERE status = 'shipped'
UNION ALL
SELECT * FROM orders WHERE priority = 'high';
Rows matching both conditions come back twice. This is a data bug, not a performance trade.
See it verified against SQLite
One order is both shipped and high priority.
Given these rows
orders
id
status
priority
1
shipped
high
2
shipped
low
3
new
high
The query
SELECT 'OR' AS form, COUNT(*) AS rows_out FROM orders WHERE status='shipped' OR priority='high'
UNION ALL
SELECT 'UNION ALL, no guard', COUNT(*) FROM (
SELECT id FROM orders WHERE status='shipped' UNION ALL SELECT id FROM orders WHERE priority='high')
UNION ALL
SELECT 'UNION ALL, guarded', COUNT(*) FROM (
SELECT id FROM orders WHERE status='shipped'
UNION ALL SELECT id FROM orders WHERE priority='high' AND status <> 'shipped');
Returns
form
rows_out
OR
3
UNION ALL, no guard
4
UNION ALL, guarded
3
The answer most people give
"Always split ORs into UNIONs, it is faster." It is a targeted fix for a specific plan problem. Applied blindly it triples the SQL, invites a double-counting bug, and often loses to the OR anyway once the dedup cost is counted.
They’ll ask next
What if the two conditions are on the same column — `status = 'a' OR status = 'b'`? Does the reasoning still hold?
You need each customer plus their order count. Scalar subquery in the SELECT list, LEFT JOIN to a grouped subquery, or a window function?
Why they ask this
Three shapes that return the same numbers with very different behaviour once a second measure is added — which it always is.
Say this
LEFT JOIN to a pre-aggregated subquery. It scales to a second and third measure without re-scanning, and it keeps the aggregation grain explicit. A scalar subquery is fine for exactly one measure and becomes a separate pass per measure after that.
The reasoning
The scalar subquery reads well and is the natural first draft. Its problem is that each additional measure is another correlated subquery — three measures means three passes over the orders table, and the optimizer can only sometimes merge them. It also silently returns NULL rather than 0 for a customer with no orders, which is usually not what a count should say.
The LEFT JOIN to a grouped derived table aggregates orders once, at a stated grain, and joins that in. Adding `SUM(amount)` costs nothing extra. The `COALESCE(o.n, 0)` on the outside is explicit about what "no orders" means, which is a decision worth making visible.
The window-function version is the wrong tool here: it computes per row and then you have to collapse, so you pay for both. Windows earn their place when you need the aggregate *beside* the detail rows, not when you are producing one row per customer.
The formulations
LEFT JOIN to a grouped subqueryship
SELECT c.id, COALESCE(o.n, 0) AS orders
FROM customers c
LEFT JOIN (SELECT customer_id, COUNT(*) AS n FROM orders GROUP BY customer_id) o
ON o.customer_id = c.id;
One pass over orders, and a second measure is one more column in the subquery.
Scalar subqueryworks
SELECT c.id,
(SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders
FROM customers c;
Clear for exactly one measure. Each further measure is another correlated pass, and it returns NULL for no matches unless you wrap it.
Window over a joinavoid
SELECT DISTINCT c.id, COUNT(*) OVER (PARTITION BY c.id) AS orders
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id;
Fans out, computes per row, then collapses with DISTINCT. Two costs to reach one row per customer.
The answer most people give
"They are all the same, the optimizer rewrites them." It often does for one measure. The shapes diverge the moment a second aggregate appears, and that is the version that ends up in production.
They’ll ask next
A customer with no orders — what does each version return, and which answer do you want in a report?
Joins & set opsQuery plans & costDialect specifics
You need to filter by a set of 5,000 ids. Inline them as an IN list, or load them into a table and join?
Why they ask this
Everyone writes the IN list first. Knowing where it stops working — and that the limit is about parsing, not matching — is an operational detail people only learn by hitting it.
Say this
Join to a table. A large literal IN list has to be parsed and planned as a literal every execution, it defeats plan caching because each distinct list is a new query, and most engines have a hard limit somewhere in the low thousands.
The reasoning
The cost of a huge IN list is paid before any data is touched. The parser builds a node per element, the planner produces a plan specific to those exact values, and the plan cache stores it under a key nobody will ever reuse — the next call has 4,999 of the same ids and one different one, so it plans from scratch again.
Loading the ids into a temporary or staging table changes the query into a join against a set, which is a stable, cacheable shape. It also lets the optimizer use statistics: it knows roughly how many rows are in the table and can pick a hash join or an index lookup accordingly, which it cannot do for an opaque literal list.
Below a few dozen values, the IN list is genuinely better — no round trip to populate anything, and the planner can use the constants for pruning. The judgment is knowing there is a crossover, and roughly where it sits for your engine.
The formulations
Join to a staged setship
SELECT o.*
FROM orders o
JOIN target_ids t ON t.id = o.id;
Stable plan shape, cacheable, and the optimizer gets cardinality information it can use.
Small literal IN listworks
SELECT * FROM orders WHERE id IN (1, 2, 3, 4, 5);
Correct and often optimal below a few dozen values — the constants help pruning.
Five thousand literalsavoid
SELECT * FROM orders WHERE id IN (1, 2, 3, /* ...4,996 more... */ 5000);
Parse and plan cost per execution, a cache entry nobody reuses, and a hard limit on several engines.
The answer most people give
"IN is fine, the database handles it." It handles it until it does not, and the failure is a parser or parameter-limit error that looks nothing like a data problem — which is why it usually happens in production with a larger list than anyone tested.
They’ll ask next
The ids come from another query in the same database. Does anything change?
"The three most recent orders per customer." Window function, correlated subquery, lateral join, or DISTINCT ON. Which do you write?
Why they ask this
The single most-asked SQL pattern above junior level, and the four solutions have genuinely different performance profiles rather than being stylistic variants.
Say this
ROW_NUMBER in a subquery, filtered to <= 3, as the portable default. If the group count is small and the per-group index is good, a lateral join can be dramatically faster because it stops after three rows per customer instead of ranking everything.
The reasoning
The window version makes one pass, assigns a rank within each partition, and filters. It is portable, it reads clearly, and its cost is the sort — which it pays for every row, including the ones that will not survive the filter.
The lateral join (`LATERAL` in Postgres, `CROSS APPLY` in SQL Server) inverts that: for each customer, fetch the top three directly. With an index on `(customer_id, ordered_at DESC)` this touches three rows per customer instead of sorting the whole table. On a million orders across a thousand customers that is a different order of magnitude — and on a hundred customers with ten thousand orders each, more so.
Postgres's `DISTINCT ON` is the most concise for N = 1 and does not generalise past it. The correlated subquery with `IN (SELECT ... LIMIT 3)` works, but it re-executes per row unless the optimizer decorrelates it, and it is the hardest of the four to read.
The tie-breaking question applies to all four: two orders at the same instant means the "top three" is ambiguous, and every version needs a second ORDER BY column to be reproducible.
The formulations
ROW_NUMBER in a subqueryship
SELECT * FROM (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC, id) AS rn
FROM orders o
) WHERE rn <= 3;
Portable, readable, one pass. Pays a full sort even for rows that will be discarded.
Lateral / CROSS APPLYship
SELECT c.id, o.*
FROM customers c
CROSS JOIN LATERAL (
SELECT * FROM orders o WHERE o.customer_id = c.id
ORDER BY o.ordered_at DESC, o.id LIMIT 3
) o;
With the right index this reads three rows per customer instead of sorting everything. Not portable to every engine.
DISTINCT ON (Postgres)works
SELECT DISTINCT ON (customer_id) *
FROM orders ORDER BY customer_id, ordered_at DESC, id;
The most concise answer for N = 1, and it does not extend to N = 3.
Correlated IN with LIMITavoid
SELECT * FROM orders o
WHERE o.id IN (
SELECT id FROM orders x WHERE x.customer_id = o.customer_id
ORDER BY x.ordered_at DESC, x.id LIMIT 3);
Works, re-executes per row unless decorrelated, and is the hardest of the four to review.
The answer most people give
"Use RANK() so ties are handled fairly." RANK returns four rows when three are tied for third, so "top 3" can return five. If that is what you want, say so — but it is a different requirement, and it breaks anything downstream expecting three.
They’ll ask next
A thousand customers with ten thousand orders each. Which version do you expect to win, and what index makes it win?
DeduplicationWindow functionsGROUP BY semanticsDialect specifics
Keep one row per order id, the most recently updated. ROW_NUMBER, GROUP BY with MAX, a self anti-join, or DISTINCT?
Why they ask this
Three of these are defensible and one is a trap. The GROUP BY version in particular looks right and quietly mixes columns from different rows.
Say this
ROW_NUMBER partitioned by the key, ordered by the survivorship rule plus a stable tiebreaker. The GROUP BY + MAX version is the interesting one: it is *portable-looking* and its behaviour differs per engine, which is worse than being uniformly wrong.
The reasoning
`SELECT order_id, MAX(updated_at), status FROM ... GROUP BY order_id` asks for a column that grouping cannot determine, and the three families of engine answer differently. PostgreSQL and most warehouses reject it. MySQL outside strict mode accepts it and returns an arbitrary status. SQLite documents an extension: when the query has a single `min()` or `max()`, the bare columns are taken from the row that produced it — so on SQLite this idiom genuinely works.
That is the worst possible situation for a query you intend to move between engines. It is correct on your laptop, correct in the MySQL staging environment by luck, and rejected by the warehouse — or, on the engine that accepts it silently, quietly wrong. The snippet below shows the SQLite guarantee holding, and then shows it evaporating the moment the min/max is removed: the same bare column flips to the other row.
The self anti-join — keep rows for which no newer row with the same key exists — is portable and correct, and is genuinely fast where the index supports it. Its weakness is ties: if two rows share the maximum timestamp, neither has a strictly newer partner and both survive.
ROW_NUMBER is the one to default to because it makes both decisions explicit and visible, and it means the same thing on every engine: the PARTITION BY names the key that defines a duplicate, and the ORDER BY names the survivorship rule. Add a stable tiebreaker or the query is non-deterministic between runs.
`SELECT DISTINCT` is not deduplication by key at all — it deduplicates on the whole row, so a replayed record differing only in an ingestion timestamp survives twice.
The formulations
ROW_NUMBERship
SELECT * FROM (
SELECT o.*, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC, id DESC) AS rn
FROM orders o
) WHERE rn = 1;
The key and the survivorship rule are both stated. Deterministic once the tiebreaker is there.
Self anti-joinworks
SELECT o.* FROM orders o
WHERE NOT EXISTS (
SELECT 1 FROM orders n
WHERE n.order_id = o.order_id AND n.updated_at > o.updated_at);
Correct and index-friendly, but keeps every row tied on the maximum.
GROUP BY + MAXavoid
SELECT order_id, MAX(updated_at) AS updated_at, status
FROM orders GROUP BY order_id;
Rejected by PostgreSQL, arbitrary on MySQL, and correct on SQLite by documented extension. Portable-looking and not portable.
SELECT DISTINCTavoid
SELECT DISTINCT * FROM orders;
Deduplicates on every column, so a replay differing in one metadata field survives twice.
See it verified against SQLite
Order 1 was updated twice. ROW_NUMBER is right everywhere; the MAX version is right here only because SQLite documents that extension — remove the MAX and the same bare column flips to the older row.
Given these rows
orders
order_id
updated_at
status
1
2026-03-01
new
1
2026-03-02
shipped
The query
SELECT 'ROW_NUMBER (portable)' AS form, status FROM (
SELECT status, ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY updated_at DESC) rn FROM orders)
WHERE rn = 1
UNION ALL
SELECT 'GROUP BY + MAX (SQLite extension)', status FROM (
SELECT MAX(updated_at) AS m, status FROM orders GROUP BY order_id)
UNION ALL
SELECT 'GROUP BY, no min/max (arbitrary)', status FROM (
SELECT status FROM orders GROUP BY order_id);
Returns
form
status
ROW_NUMBER (portable)
shipped
GROUP BY + MAX (SQLite extension)
shipped
GROUP BY, no min/max (arbitrary)
new
The answer most people give
"GROUP BY the key and take MAX of the timestamp — it works." It works on SQLite because SQLite promises it, and nowhere else does. PostgreSQL rejects the query and MySQL returns whichever row it felt like. An idiom whose correctness depends on which engine you ran it in is not an idiom you can ship.
They’ll ask next
Two rows share the maximum updated_at. What does each of your four versions return?
You need paid, refunded and cancelled totals as three columns. Conditional aggregation, three LEFT JOINs to filtered subqueries, or FILTER?
Why they ask this
The multi-join version is what people write when they think in tables rather than in aggregates, and it is the one that breaks when a category has no rows.
Say this
Conditional aggregation — one pass, one GROUP BY, one CASE per column. Three LEFT JOINs read the fact table three times and turn a missing category into a NULL you have to remember to coalesce.
The reasoning
Conditional aggregation makes a single pass and expresses each column as what it is: a restricted sum. Adding a fourth status is one more line, not one more join. The `FILTER (WHERE ...)` clause is the standard spelling of the same idea and reads better where it exists — Postgres and SQLite have it; many warehouses do not.
The multi-join version has a subtler problem than cost. Each subquery is grouped independently, so a customer appearing in one but not another produces NULLs, and if you join them to each other rather than to a driving dimension you can lose rows entirely. It also multiplies if any subquery is not actually unique per key.
One thing to be careful of: `SUM(CASE WHEN ... THEN amount END)` returns NULL, not 0, for a group with no matching rows. Whether you want NULL or 0 is a real decision — NULL says "nothing to measure", 0 says "measured and it was nothing" — and wrapping in COALESCE should be deliberate rather than reflexive.
The formulations
Conditional aggregationship
SELECT customer_id,
SUM(CASE WHEN status='paid' THEN amount END) AS paid,
SUM(CASE WHEN status='refunded' THEN amount END) AS refunded,
SUM(CASE WHEN status='cancelled' THEN amount END) AS cancelled
FROM orders GROUP BY customer_id;
One pass, one grain, and a fourth status is one more line.
FILTER clauseship
SELECT customer_id,
SUM(amount) FILTER (WHERE status = 'paid') AS paid,
SUM(amount) FILTER (WHERE status = 'refunded') AS refunded
FROM orders GROUP BY customer_id;
The standard spelling and the clearest to read — where the engine supports it.
Three LEFT JOINsavoid
SELECT c.id, p.total AS paid, r.total AS refunded
FROM customers c
LEFT JOIN (SELECT customer_id, SUM(amount) total FROM orders WHERE status='paid' GROUP BY 1) p ON p.customer_id=c.id
LEFT JOIN (SELECT customer_id, SUM(amount) total FROM orders WHERE status='refunded' GROUP BY 1) r ON r.customer_id=c.id;
Reads the fact table once per category, and every new status is another join.
See it verified against SQLite
A customer with no refunds: SUM(CASE ...) gives NULL, not zero.
Given these rows
orders
customer_id
status
amount
1
paid
10
1
paid
5
The query
SELECT customer_id,
SUM(CASE WHEN status='paid' THEN amount END) AS paid,
SUM(CASE WHEN status='refunded' THEN amount END) AS refunded
FROM orders GROUP BY customer_id;
Returns
customer_id
paid
refunded
1
15
NULL
The answer most people give
"Wrap everything in COALESCE(..., 0) so the report looks clean." Sometimes right, sometimes a lie. A zero asserts you measured the category and found nothing; NULL says the category never appeared. On a refund column those are very different statements.
They’ll ask next
Which of the two would you rather see in a column that feeds an average?
Each category's share of overall revenue. Window function, self-join to a totals subquery, or a scalar subquery in the SELECT?
Why they ask this
The window version is a one-liner that a lot of people never learned, and the alternatives all scan the fact table twice.
Say this
A window function: `SUM(revenue) / SUM(SUM(revenue)) OVER ()`. The nested aggregate looks strange the first time but computes the grand total from the already-grouped rows, so the fact table is read once.
The reasoning
The inner `SUM(revenue)` is the per-category aggregate; the outer `SUM(...) OVER ()` sums those group results across the whole result set. Window functions are evaluated after GROUP BY, which is precisely what makes this legal and what makes it cheap — it is summing a handful of category rows, not millions of fact rows.
The self-join and scalar-subquery versions both compute the grand total by reading the fact table a second time. On a warehouse that is a second scan of the largest object in the query, and it is entirely avoidable.
Watch the division. Integer division truncates in several engines, so a share comes back as 0 — cast one side to a float or use a decimal type. And guard the denominator: a filtered result set that ends up empty makes the total zero, and dividing by it either errors or returns NULL depending on the engine.
The formulations
Window over the grouped rowsship
SELECT category,
SUM(revenue) AS revenue,
SUM(revenue) * 1.0 / SUM(SUM(revenue)) OVER () AS share
FROM sales GROUP BY category;
One pass. The outer window sums the group results, not the source rows.
Cross join to a totals subqueryworks
SELECT s.category, s.revenue, s.revenue * 1.0 / t.total AS share
FROM (SELECT category, SUM(revenue) revenue FROM sales GROUP BY category) s
CROSS JOIN (SELECT SUM(revenue) total FROM sales) t;
Explicit and portable to engines without window functions. Reads the fact table twice.
Scalar subquery per rowavoid
SELECT category, SUM(revenue) AS revenue,
SUM(revenue) * 1.0 / (SELECT SUM(revenue) FROM sales) AS share
FROM sales GROUP BY category;
Usually hoisted by the optimizer, but it hides a second full aggregate inside a projection.
The answer most people give
"You cannot nest aggregates." You cannot nest one aggregate inside another aggregate — but `SUM(SUM(x)) OVER ()` is an aggregate inside a *window*, which is legal precisely because windows run after grouping.
They’ll ask next
Now I want share within each region rather than overall. What changes?
A five-step transformation. Chain of CTEs, nested subqueries, or a sequence of temp tables?
Why they ask this
It is a readability question that people answer as a performance question, and the right answer depends on facts about the data that a candidate should ask for.
Say this
A CTE chain, by default — it reads top to bottom and each step can be selected in isolation while debugging. Reach for temp tables when a step is reused several times, when it is large enough that materializing beats recomputing, or when the optimizer is making a bad choice across the whole thing.
The reasoning
Nested subqueries force the reader to work inside out, and by three levels deep nobody can hold the shape. The same logic as a CTE chain reads in execution order and each name documents what that step produced. This is the single biggest lever on whether a long query survives review.
The performance question is genuinely engine-dependent and worth saying so. Where CTEs are inlined, the chain is free. Where a step is referenced several times and each reference recomputes it, materializing into a temp table can turn five scans into one. Postgres lets you say `MATERIALIZED` explicitly; elsewhere a temp table is the way to force it.
Temp tables buy one more thing that neither alternative offers: statistics. The optimizer knows how many rows a temp table has, where it can only guess for an inlined subquery. On a step whose row count is wildly different from the estimate, that alone can change the plan for everything downstream.
The formulations
CTE chainship
WITH cleaned AS (...),
joined AS (SELECT ... FROM cleaned ...),
ranked AS (SELECT ... FROM joined ...)
SELECT * FROM ranked;
Reads in execution order; each step is separately selectable while debugging.
Temp tablesworks
CREATE TEMP TABLE cleaned AS SELECT ...;
CREATE TEMP TABLE joined AS SELECT ... FROM cleaned ...;
SELECT * FROM joined;
Materializes once and gives the optimizer real statistics. The right call for a reused or badly estimated step.
Nested subqueriesavoid
SELECT * FROM (
SELECT * FROM (
SELECT * FROM (...) a
) b
) c;
Same plan, unreadable past two levels. Nobody can review it and nobody can debug a middle step.
The answer most people give
"CTEs are slower, always use subqueries." That was one engine before 2019. Today the difference is usually nothing, and where it is not, the fix is to say what you want explicitly rather than to write worse SQL preemptively.
They’ll ask next
One of the five steps is referenced by three later steps. Does that change your answer?
You need every descendant of a node in an org chart. Recursive CTE, repeated self-joins, or a closure table?
Why they ask this
The recursive CTE is the textbook answer and the closure table is the production one. Knowing both, and when the second is worth its cost, is the senior answer.
Say this
A recursive CTE for correctness at any depth. If the traversal is hot and the hierarchy changes rarely, precompute a closure table — one row per ancestor-descendant pair — and the query becomes a plain indexed lookup.
The reasoning
A fixed chain of self-joins only works if you know the maximum depth, and it silently truncates when the tree gets deeper. That is the version to reject: it looks like it works because your test data is three levels deep.
The recursive CTE handles arbitrary depth and is standard SQL. Two things to say about it unprompted: it needs a termination guarantee, because a cycle in what you assumed was a tree loops forever — most engines let you cap the depth, and a visited-path check is the general fix. And each level is a separate join, so deep traversals on hot paths get expensive.
A closure table trades write cost for read cost: store every ancestor-descendant pair with its distance, and "all descendants" becomes a single indexed range scan. It multiplies storage and every hierarchy change has to maintain it, which is why it fits slowly-changing structures like org charts and category trees rather than fast-moving graphs.
The formulations
Recursive CTEship
WITH RECURSIVE sub AS (
SELECT id, manager_id, 1 AS depth FROM employees WHERE id = :root
UNION ALL
SELECT e.id, e.manager_id, s.depth + 1
FROM employees e JOIN sub s ON e.manager_id = s.id
)
SELECT * FROM sub;
Correct at any depth and portable. Needs a cycle guard if the data is not a guaranteed tree.
Closure tableworks
SELECT descendant_id, distance
FROM employee_closure WHERE ancestor_id = :root;
Reads become a single indexed lookup. Costs storage and maintenance on every hierarchy change.
Fixed self-joinsavoid
SELECT ... FROM employees e1
LEFT JOIN employees e2 ON e2.manager_id = e1.id
LEFT JOIN employees e3 ON e3.manager_id = e2.id;
Truncates silently once the tree is deeper than the number of joins you wrote.
See it verified against SQLite
Four levels deep — a recursive CTE finds all of them.
Given these rows
emp
id
mgr
1
NULL
2
1
3
2
4
3
The query
WITH RECURSIVE sub(id, depth) AS (
SELECT id, 1 FROM emp WHERE id = 1
UNION ALL
SELECT e.id, s.depth + 1 FROM emp e JOIN sub s ON e.mgr = s.id
)
SELECT id, depth FROM sub ORDER BY depth;
Returns
id
depth
1
1
2
2
3
3
4
4
The answer most people give
"Recursive CTEs are always slow, denormalize instead." Denormalizing is a real option with a real cost — every write now maintains the closure table. Choosing it without saying what it costs on the write path is half an answer.
They’ll ask next
Someone sets an employee as their own manager's manager. What does your recursive CTE do?
CTEs & recursionGROUP BY semanticsDialect specifics
The same complicated expression appears in SELECT, WHERE and ORDER BY. Repeat it, alias it, or push it into a CTE?
Why they ask this
It exposes whether someone knows the logical processing order well enough to predict where an alias is visible — and that "the optimizer will handle it" is not always true.
Say this
Compute it once in a subquery or CTE and reference the name. You cannot use a SELECT alias in WHERE because SELECT has not been evaluated yet, and repeating a non-deterministic or expensive expression risks the engine evaluating it several times.
The reasoning
The alias question is decided by logical processing order: WHERE runs before SELECT, so an alias defined in SELECT is not visible there. ORDER BY runs after, so it is. GROUP BY is dialect-dependent — Postgres permits an alias there, others do not. Knowing this without checking is a reliable signal.
For pure deterministic expressions, most optimizers evaluate a repeated expression once anyway, so repetition is a readability problem rather than a performance one. The exceptions matter: a volatile function such as `random()` or `now()` may genuinely be evaluated per occurrence, and a user-defined function the optimizer cannot prove is deterministic will be.
Wrapping in a CTE or subquery makes it unambiguous and gives the expression a name, which is usually worth more than either concern. The name is documentation — `net_amount` in three places beats the same twelve-token arithmetic in three places, whatever the plan does.
The formulations
Name it in a subqueryship
SELECT * FROM (
SELECT o.*, (gross - discount) * (1 + tax_rate) AS net
FROM orders o
) WHERE net > 100 ORDER BY net DESC;
Computed once, named once, visible everywhere downstream.
Repeat itworks
SELECT (gross - discount) * (1 + tax_rate) AS net FROM orders
WHERE (gross - discount) * (1 + tax_rate) > 100
ORDER BY (gross - discount) * (1 + tax_rate) DESC;
Usually the same plan for a deterministic expression. Three places to update when the formula changes.
Alias referenced in WHEREavoid
SELECT (gross - discount) AS net FROM orders WHERE net > 100;
Does not parse in standard SQL — WHERE is evaluated before SELECT, so the name does not exist yet.
The answer most people give
"Just alias it, the alias works everywhere." It works in ORDER BY, sometimes in GROUP BY and HAVING depending on the dialect, and never in WHERE. That is not a quirk — it falls straight out of the processing order.
They’ll ask next
Does your answer change if the expression calls a UDF?
Forty status codes map to eight display labels. A CASE ladder in the query, a mapping table joined in, or a view?
Why they ask this
It is a modelling question wearing a syntax costume. The CASE ladder is faster to write and is the thing that gets copied into fifteen queries.
Say this
A mapping table. Business rules that change should be data, not code — and once the same ladder exists in more than one query, they will disagree the first time somebody adds a code.
The reasoning
The CASE ladder is right for two or three cases used in one place. At forty codes it is a lookup table written in the wrong language: it cannot be queried to ask "what maps to Cancelled", it cannot be updated without a deployment, and nothing stops two copies drifting apart.
A mapping table makes the rule inspectable and joinable. The one thing to be careful about is the join type — an inner join silently drops rows whose code is missing from the mapping, which is exactly the case you want to hear about. Use a LEFT JOIN and either default the label or add an assertion that no code is unmapped.
A view over the join is often the best of both: consumers select from something that already carries the label, and the mapping stays in one place. That is also the natural place for the "unmapped" check to live, so a new code raises a data-quality alert rather than quietly appearing as NULL in a dashboard.
The formulations
Mapping table, LEFT JOINship
SELECT o.*, COALESCE(m.label, 'Unmapped') AS status_label
FROM orders o
LEFT JOIN status_map m ON m.code = o.status_code;
The rule is data. LEFT JOIN plus a default means a new code shows up instead of vanishing.
CASE ladder, few cases, one placeworks
SELECT CASE status_code WHEN 'S' THEN 'Shipped' WHEN 'C' THEN 'Cancelled' ELSE 'Other' END AS label
FROM orders;
Fine for a handful of cases in a single query. The ELSE is what stops a new code becoming NULL.
Mapping table, INNER JOINavoid
SELECT o.*, m.label FROM orders o JOIN status_map m ON m.code = o.status_code;
A code missing from the mapping silently deletes the order from the result.
The answer most people give
"CASE is faster than a join." On forty branches against a tiny lookup table the difference is noise, and it is the wrong axis — the cost here is maintenance, and it is paid by whoever adds code forty-one.
They’ll ask next
A new status code appears in production tomorrow. What does each version do, and which one tells you?
"Insert the row, or update it if the key already exists." MERGE, INSERT … ON CONFLICT, or DELETE then INSERT?
Why they ask this
All three are in production somewhere, and the delete-then-insert version is the one that loses data when the job dies between the two statements.
Say this
MERGE or ON CONFLICT, depending on the engine — both are a single atomic statement. DELETE-then-INSERT is two statements, so a failure between them leaves the row gone, and it also destroys any column the insert does not repopulate.
The reasoning
The single-statement forms are atomic by construction: the row is either updated or inserted, and there is no window in which it does not exist. `INSERT … ON CONFLICT DO UPDATE` (Postgres, SQLite) and `MERGE` (the standard, and most warehouses) express the same intent.
DELETE-then-INSERT has two failure modes. The obvious one is a crash between the statements — inside a transaction the delete rolls back, but a lot of pipelines run these as separate autocommitted statements. The subtle one is that it is a *replace*, not an update: any column the insert does not supply reverts to its default, so a `created_at` or an enrichment written by another job is silently lost.
For MERGE specifically, two preconditions are worth stating unprompted. The source must have at most one row per key — several engines error on a multi-match and at least one picks arbitrarily. And the update should be an assignment rather than an accumulation, or a replayed batch double-counts.
The formulations
INSERT … ON CONFLICT DO UPDATEship
INSERT INTO targets (id, status, amount)
VALUES (:id, :status, :amount)
ON CONFLICT (id) DO UPDATE
SET status = excluded.status, amount = excluded.amount;
One atomic statement. `excluded` is the row that failed to insert, which is what you are merging in.
MERGEship
MERGE INTO targets t
USING source s ON t.id = s.id
WHEN MATCHED AND s.version > t.version THEN UPDATE SET status = s.status
WHEN NOT MATCHED THEN INSERT (id, status, version) VALUES (s.id, s.status, s.version);
The standard form, and the version guard is what makes a replayed batch harmless.
DELETE then INSERTavoid
DELETE FROM targets WHERE id = :id;
INSERT INTO targets (id, status) VALUES (:id, :status);
Two statements: a crash between them loses the row, and every column the insert omits is reset.
See it verified against SQLite
ON CONFLICT updates in place; the row keeps the column the upsert did not touch.
Given these rows
targets
id
status
created_at
1
new
2026-01-01
The query
INSERT INTO targets (id, status) VALUES (1, 'shipped')
ON CONFLICT (id) DO UPDATE SET status = excluded.status;
SELECT id, status, created_at FROM targets;
Returns
id
status
created_at
1
shipped
2026-01-01
The answer most people give
"Delete then insert is simpler and it is the same thing." It is the same thing only when the insert supplies every column and nothing fails in between. Neither is usually true, and both failures are silent.
They’ll ask next
The same batch is delivered twice. Which of the three leaves the table in the same state, and what did you have to add to make that true?
Set each order's region from a lookup table. `UPDATE … FROM`, a correlated subquery, or MERGE?
Why they ask this
The correlated-subquery version has a failure mode people do not expect: it sets non-matching rows to NULL rather than leaving them alone.
Say this
`UPDATE … FROM` with a join, or MERGE. The correlated subquery form updates *every* row — rows with no match get NULL — unless you repeat the condition in a WHERE EXISTS, which everyone forgets.
The reasoning
The correlated `SET region = (SELECT ...)` looks surgical and is not. The UPDATE has no WHERE clause, so it visits every row; for the rows where the subquery finds nothing, the scalar subquery returns NULL and the column is overwritten with it. Adding `WHERE EXISTS (same correlation)` fixes it, and the duplication of the condition is exactly why this form is error-prone.
`UPDATE … FROM` (Postgres, SQL Server, SQLite 3.33+) joins in the source and only touches matching rows, which is what was meant. Its own hazard is the reverse of the subquery's: if the source has more than one row per target, the engine picks one arbitrarily and does not tell you. That is a real non-determinism, not a theoretical one.
MERGE is the most explicit of the three because it forces you to state what happens when matched and when not matched, and several engines will raise an error rather than silently choosing among duplicate source rows. Where a job is going to run unattended, that error is a feature.
The formulations
UPDATE … FROMship
UPDATE orders
SET region = r.region
FROM region_map r
WHERE r.code = orders.region_code;
Only matching rows are touched. Verify the source is unique per key first.
MERGEship
MERGE INTO orders o USING region_map r ON r.code = o.region_code
WHEN MATCHED THEN UPDATE SET region = r.region;
States the matched and unmatched behaviour explicitly, and errors on a multi-match on several engines.
Correlated subquery, guardedworks
UPDATE orders
SET region = (SELECT r.region FROM region_map r WHERE r.code = orders.region_code)
WHERE EXISTS (SELECT 1 FROM region_map r WHERE r.code = orders.region_code);
Correct and portable, at the cost of writing the correlation twice.
Correlated subquery, unguardedavoid
UPDATE orders
SET region = (SELECT r.region FROM region_map r WHERE r.code = orders.region_code);
Visits every row and NULLs out the ones with no match. The most common destructive UPDATE there is.
See it verified against SQLite
Order 2 has no mapping. The unguarded subquery erases its existing region.
Given these rows
orders
id
region_code
region
1
E
old
2
Z
old
region_map
code
region
E
EMEA
The query
UPDATE orders SET region = (SELECT r.region FROM region_map r WHERE r.code = orders.region_code);
SELECT id, region_code, region FROM orders ORDER BY id;
Returns
id
region_code
region
1
E
EMEA
2
Z
NULL
The answer most people give
"The subquery only updates rows that match." It updates every row in the table — the subquery decides the *value*, not which rows are visited. That distinction has erased a lot of production columns.
They’ll ask next
The lookup table has two rows for code E. What does UPDATE … FROM do, and what does MERGE do?
You need to remove 200 million rows from a billion-row table. DELETE, TRUNCATE, partition drop, or rebuild?
Why they ask this
The naive DELETE is not merely slow — it can hold locks for hours, blow out the transaction log and leave the table bloated afterwards. This is an operational question with a modelling answer.
Say this
If the rows line up with partitions, drop the partitions — it is a metadata operation. If they do not, either delete in batches with commits between them, or rebuild the table with a CTAS of the rows you are keeping and swap it in.
The reasoning
A single DELETE of 200 million rows writes every one of them to the undo or WAL log, holds locks for the duration, and on MVCC engines leaves dead tuples that still occupy space until a vacuum runs. The transaction may not even fit. Nothing about the statement warns you.
Batched deletes — a loop of `DELETE … WHERE id IN (SELECT id … LIMIT 50000)` with a commit between iterations — keep each transaction small and let other work proceed. The trade is that the operation is no longer atomic, so it must be restartable and idempotent, which in practice means the predicate has to be stable as rows disappear.
CTAS-and-swap is often fastest when you are deleting a large fraction: write the survivors to a new table, build the indexes, then rename. You pay double the storage briefly and you need a plan for writes arriving during the copy.
TRUNCATE only helps for *all* rows. It is worth naming the difference: it is DDL rather than DML on most engines, it does not fire row triggers, it cannot be filtered, and on some engines it is not transactional — so it is not a fast DELETE, it is a different operation.
The formulations
Drop the partitionsship
ALTER TABLE events DROP PARTITION FOR (DATE '2024-01-01');
Metadata only. This is the reason to partition on the column you delete by.
Batched delete with commitsworks
-- repeat until zero rows affected
DELETE FROM events
WHERE id IN (SELECT id FROM events WHERE created_at < :cutoff LIMIT 50000);
COMMIT;
Bounded transactions and bounded locks. No longer atomic, so it must be restartable.
CTAS and swapworks
CREATE TABLE events_new AS SELECT * FROM events WHERE created_at >= :cutoff;
-- build indexes, then:
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;
Usually fastest when most rows go. Needs double storage and a story for concurrent writes.
One big DELETEavoid
DELETE FROM events WHERE created_at < :cutoff;
Hours of locks, a transaction log the size of the deletion, and a bloated table afterwards.
The answer most people give
"Use TRUNCATE, it is the fast DELETE." TRUNCATE cannot take a WHERE clause. It empties the table, and reaching for it here means deleting the 800 million rows you meant to keep.
They’ll ask next
What would you have changed about the table design so this was a partition drop?
A load writes to three tables. One transaction around all of it, one per table, or one per batch of rows?
Why they ask this
It forces a candidate to trade atomicity against lock duration and restartability out loud, which is the actual daily judgment of running a pipeline.
Say this
One transaction around all three if they must be consistent with each other and the volume permits — a partially loaded set of related tables is the worst outcome. If the volume does not permit it, batch within each table and make the whole load idempotent so a partial run can simply be repeated.
The reasoning
Atomicity is the reason to widen the transaction: if a downstream reader can see the fact table updated but not the dimension, it will produce wrong answers and you will not know which run caused it. That argues for one transaction.
Everything else argues for narrowing it. A long transaction holds locks, grows the undo log, blocks vacuum or compaction, and on failure has to roll back work that took an hour. And the longer it is, the higher the chance something unrelated kills it.
The resolution in most pipelines is not to choose but to change the problem: make the load idempotent so that atomicity matters less. If re-running a batch produces the same state, a crash halfway is recoverable by running it again, and you can afford small transactions. That is why idempotency and transaction sizing are the same conversation.
The one thing not to do is commit per row. Each commit is a durability barrier — an fsync in most engines — so a million-row load becomes a million disk syncs, and it gives up atomicity for nothing.
The formulations
One transaction, if it fitsship
BEGIN;
INSERT INTO dim_customer ...;
INSERT INTO fact_orders ...;
INSERT INTO fact_lines ...;
COMMIT;
Readers never see half a load. The right default whenever the volume allows.
Batched, with an idempotent loadship
-- per chunk, each safely repeatable
BEGIN;
MERGE INTO fact_orders ... ; -- keyed, version-guarded
COMMIT;
Bounded locks and restartable. Requires the load to be idempotent, which is worth building anyway.
Commit per rowavoid
-- inside a per-row loop
INSERT INTO fact_orders VALUES (...);
COMMIT;
A durability barrier per row, and no atomicity anywhere. The worst of both.
The answer most people give
"Wrap everything in a transaction and it is safe." A transaction gives atomicity, not idempotency. It does not stop a successful run from being applied a second time, and retry is the normal case in a pipeline.
They’ll ask next
The job fails after the second insert. Walk me through what the next run has to do under each of your options.
"Orders in March." `BETWEEN`, `>=` with `<`, or `date_trunc(month, ordered_at) = '2026-03-01'`?
Why they ask this
Two of these are subtly wrong on a timestamp column and the third stops an index being used. It is the most common filter in analytics and most people write it wrong at least once.
Say this
Half-open: `>= '2026-03-01' AND < '2026-04-01'`. BETWEEN is inclusive at both ends, so on a timestamp it either misses most of the 31st or double-counts a boundary instant, and wrapping the column in a function usually prevents an index seek.
The reasoning
`BETWEEN '2026-03-01' AND '2026-03-31'` on a timestamp column means "up to 2026-03-31 00:00:00", so everything on the last day after midnight is missing. The classic patch — ending at `2026-03-31 23:59:59` — is wrong in the other direction the moment the column has sub-second precision, and it is wrong again if the type changes.
The half-open form has none of these problems and composes: consecutive months tile the timeline with no gap and no overlap, so summing months equals the year. It is the same convention that makes window boundaries and SCD validity intervals work, and it is worth naming as a general rule rather than a date trick.
`date_trunc(...) = ...` is readable and correct, but it applies a function to the column, which makes the predicate non-sargable on most engines — the index on `ordered_at` cannot be seeked, only scanned. Some engines can use a function-based index or infer the range; relying on that is engine-specific.
And say the timezone out loud. "March" in whose zone? A UTC-stored timestamp filtered with local dates shifts every boundary by the offset, which quietly moves a few hours of revenue between months.
The formulations
Half-open rangeship
SELECT * FROM orders
WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01';
Correct at any precision, index-friendly, and consecutive ranges tile without gaps.
date_trunc equalityworks
SELECT * FROM orders
WHERE date_trunc('month', ordered_at) = DATE '2026-03-01';
Readable and correct. Non-sargable on most engines, so the index goes unused.
BETWEEN two datesavoid
SELECT * FROM orders
WHERE ordered_at BETWEEN '2026-03-01' AND '2026-03-31';
Loses everything after midnight on the 31st. The 23:59:59 patch breaks at higher precision.
See it verified against SQLite
An order late on the 31st: BETWEEN misses it, the half-open range does not.
Given these rows
orders
id
ordered_at
1
2026-03-15 10:00
2
2026-03-31 18:30
The query
SELECT 'BETWEEN' AS form, COUNT(*) AS rows_out FROM orders
WHERE ordered_at BETWEEN '2026-03-01' AND '2026-03-31'
UNION ALL
SELECT 'half-open', COUNT(*) FROM orders
WHERE ordered_at >= '2026-03-01' AND ordered_at < '2026-04-01';
Returns
form
rows_out
BETWEEN
1
half-open
2
The answer most people give
"BETWEEN is inclusive so it covers the whole month." It is inclusive of the *value* `2026-03-31`, which as a timestamp is midnight. Everything that happened during the last day is outside the range.
They’ll ask next
The column is a DATE rather than a TIMESTAMP. Does BETWEEN become correct?
"The longest streak of consecutive active days per user." Window difference, self-join, or recursive CTE?
Why they ask this
The gaps-and-islands trick is one of the few genuinely non-obvious SQL techniques, and someone who knows it has solved a real problem with it.
Say this
The window-difference trick: subtract a ROW_NUMBER from the date, and consecutive days collapse to a constant you can group on. It is one pass; the self-join is quadratic and the recursive CTE is a row at a time.
The reasoning
The insight is that for a run of consecutive days, the date and the row number both increase by one, so their difference is constant across the run and changes at every gap. Grouping by that difference turns "find the runs" into an ordinary GROUP BY, and the streak length is just the count per group.
The general version, when the gap rule is not "exactly one day", is the two-step form: flag each row where the distance from the previous row exceeds the threshold, then take a running SUM of those flags as the group id. That handles sessionization, maintenance windows, and anything else where "consecutive" needs a definition.
The alternatives are worse in specific ways. A self-join comparing every row to every other is quadratic and falls over on real volumes. A recursive CTE walks one row at a time, which is correct and cannot be parallelised. Both are also considerably harder to read than the trick once you know the trick.
The formulations
Date minus row numbership
SELECT user_id, COUNT(*) AS streak
FROM (
SELECT user_id, day,
DATE(day, '-' || ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) || ' days') AS grp
FROM active_days
)
GROUP BY user_id, grp;
One pass. Consecutive days share a constant, so runs become groups.
Flag-and-running-sumship
SELECT user_id, SUM(is_break) OVER (PARTITION BY user_id ORDER BY day) AS grp, day
FROM (
SELECT user_id, day,
CASE WHEN LAG(day) OVER (PARTITION BY user_id ORDER BY day) = DATE(day,'-1 day')
THEN 0 ELSE 1 END AS is_break
FROM active_days
);
The general form — works for any gap rule, not just exactly one day.
Self-join on adjacencyavoid
SELECT a.user_id, COUNT(*) FROM active_days a
JOIN active_days b ON b.user_id = a.user_id AND b.day <= a.day
GROUP BY a.user_id, a.day;
Quadratic in rows per user. Correct on a test fixture, hopeless on real volume.
See it verified against SQLite
Two runs: the 1st–3rd and the 5th–6th.
Given these rows
d
u
day
u1
2026-03-01
u1
2026-03-02
u1
2026-03-03
u1
2026-03-05
u1
2026-03-06
The query
WITH g AS (SELECT u, day, DATE(day, '-' || ROW_NUMBER() OVER (PARTITION BY u ORDER BY day) || ' days') AS grp FROM d)
SELECT u, MIN(day) AS run_start, COUNT(*) AS streak FROM g GROUP BY u, grp ORDER BY run_start;
Returns
u
run_start
streak
u1
2026-03-01
3
u1
2026-03-05
2
The answer most people give
"Group by user and count the days." That gives total active days, not the longest consecutive run — a user active on alternate days for a month scores thirty rather than one.
They’ll ask next
Now the rule is "no gap longer than 3 days" rather than exactly consecutive. Which version survives?
Attach the price that was in effect when each order was placed. Interval predicate, LATERAL top-1, or a window over the union?
Why they ask this
The interval version needs a closed dimension; the lateral version does not. Knowing which you have is the difference between a correct query and a query that needs a nightly job to keep it correct.
Say this
If the dimension has `valid_from` and `valid_to`, join on the key plus a half-open interval predicate — it is one join and the optimizer handles it. If it only has `valid_from`, use a LATERAL top-1 ordered by `valid_from DESC`, which does not require the intervals to be closed.
The reasoning
The interval join is the cheapest and the clearest, and it depends on an invariant: the validity windows for a key must tile the timeline without overlapping. If they do, exactly one row matches. If they overlap by a single instant — which is what `<=` on both ends gives you — every order on a boundary matches twice and the revenue doubles.
Many dimensions only carry `valid_from`, with the end implied by the next row. Rather than materializing `valid_to` in a preprocessing step, a LATERAL top-1 asks directly for "the latest version at or before this order", which is the definition. It costs a lookup per fact row, which an index on `(key, valid_from DESC)` makes cheap.
The window-over-union approach — union the facts and dimension rows, order by time, and carry the last seen attribute forward with a window — is the one that generalises to streaming and to several dimensions at once. It is the most powerful and the least readable, and it is worth knowing exists rather than reaching for first.
The formulations
Half-open interval joinship
SELECT o.*, p.price
FROM orders o
JOIN price_history p
ON p.sku = o.sku
AND o.ordered_at >= p.valid_from
AND o.ordered_at < p.valid_to;
One join, one matching row — provided the intervals genuinely tile without overlap.
LATERAL top-1ship
SELECT o.*, p.price
FROM orders o
CROSS JOIN LATERAL (
SELECT price FROM price_history p
WHERE p.sku = o.sku AND p.valid_from <= o.ordered_at
ORDER BY p.valid_from DESC LIMIT 1
) p;
Needs no valid_to at all. The definition of "as of", written directly.
Interval join with <= on both endsavoid
... AND o.ordered_at >= p.valid_from AND o.ordered_at <= p.valid_to;
An order exactly on a boundary matches two versions. Fan-out arriving through the time dimension.
The answer most people give
"Join on the key and take the latest price." That labels every historical order with today's price. It also looks correct in testing, because a dimension with one row per key makes the two queries agree.
They’ll ask next
The dimension has overlapping validity windows for one sku. How would you detect that before it corrupts a report?
An incremental load reads rows newer than the last watermark. `>` or `>=`, and what else do you need?
Why they ask this
Both are wrong on their own and most pipelines pick one and live with it. Knowing why you need both a comparison and a set of boundary ids is a genuinely senior answer.
Say this
`>=`, plus the set of ids you already processed at exactly that watermark. `>` silently loses every row sharing the boundary timestamp; `>=` alone reprocesses them. The pair is a complete checkpoint, and either half alone is not.
The reasoning
The problem only exists because the watermark column is not unique. Sources write in batches, so dozens of rows share a timestamp to the second. If the previous run read some of them and recorded that timestamp, `>` will never return the rest — their timestamp is not greater and never will be. Those rows are gone silently.
`>=` picks them up and also re-reads the ones already processed. That is recoverable if the write is idempotent, which is why `>=` plus an idempotent merge is a legitimate, simpler design. Carrying the boundary ids removes the reprocessing as well.
Two more things belong in the answer. The predicate should be sargable — comparing the raw column, not a function of it — or the incremental read scans the table it was meant to avoid. And an empty source must not reset the watermark: recomputing it from zero selected rows is how a pipeline reprocesses its entire history at 3am.
The formulations
Greater-or-equal plus boundary idsship
SELECT * FROM source
WHERE updated_at >= :last_watermark
AND NOT (updated_at = :last_watermark AND id IN (:seen_ids));
Loses nothing and repeats nothing. The checkpoint is the pair, not the timestamp alone.
Greater-or-equal with an idempotent writeworks
SELECT * FROM source WHERE updated_at >= :last_watermark;
Reprocesses the boundary rows, which is harmless if the merge is keyed and version-guarded.
Strictly greateravoid
SELECT * FROM source WHERE updated_at > :last_watermark;
Permanently drops any row that shares the boundary timestamp and arrived after the read.
The answer most people give
"Use `>` so you never reprocess anything." Reprocessing is visible and cheap; losing rows is invisible and permanent. If you must pick one, pick the one whose failure you can see.
They’ll ask next
The source has a monotonically increasing log sequence number instead of a timestamp. Does the problem go away?
EvergreenJoins & set opsNULL logicData quality assertionsGrain & fan-out
You have an internal ledger and a bank settlement file for the same day. Write the reconciliation: what is missing on each side, and where do the amounts disagree?
Why they ask this
It is the most-asked SQL question in fintech and finance-adjacent roles, and it is one query that has to answer three different questions at once.
Say this
A FULL OUTER JOIN on the business key, then classify each row: null on the right is missing downstream, null on the left is unexpected, both present with different amounts is a mismatch.
The reasoning
**One join, three answers.** An inner join finds only the matches and tells you nothing about what is absent, which is the whole point of a reconciliation. A `FULL OUTER JOIN` on the shared key keeps every row from both sides, and then a `CASE` classifies each one: present on the left only, present on the right only, present on both but disagreeing, or agreeing. Reporting all four categories — including the count that matched — is what makes the output auditable.
**Compare on the key, not on the whole row.** The join key is the business identifier both systems agree on — a transaction reference, not a surrogate. If the two sides key differently, that mapping is the first problem to solve, and it usually is the real problem.
**Money comparisons need care.** Compare with a tolerance rather than exact equality if either side rounds, and be explicit that `amount_a <> amount_b` is *unknown* when either is null — so a null amount silently drops out of your mismatch count unless you handle it. Comparing `COALESCE`d values or adding an explicit null check is what stops a missing amount from being reported as "matched".
**Aggregate before you join if the grains differ.** One transaction in the ledger may be several lines in the settlement file. Reconciling a one-row-per-transaction table against a one-row-per-line table without collapsing the second first produces mismatches that are really fan-out, and that is the most common way a reconciliation query lies.
**Report the summary, keep the detail.** Counts and totals per category are what a daily control needs; the row-level output is what someone uses to investigate. Producing only the summary means every investigation starts by rewriting the query.
The formulations
FULL OUTER JOIN and classifyship
SELECT COALESCE(l.txn_ref, s.txn_ref) AS txn_ref,
CASE WHEN s.txn_ref IS NULL THEN 'missing_in_settlement'
WHEN l.txn_ref IS NULL THEN 'missing_in_ledger'
WHEN l.amount IS DISTINCT FROM s.amount THEN 'amount_mismatch'
ELSE 'matched' END AS status,
l.amount AS ledger_amount, s.amount AS settled_amount
FROM ledger l FULL OUTER JOIN settlement s USING (txn_ref)
One pass, four categories, and IS DISTINCT FROM survives nulls.
Collapse the grain firstship
WITH s AS (SELECT txn_ref, SUM(amount) amount
FROM settlement_lines GROUP BY txn_ref)
SELECT ... FROM ledger l FULL OUTER JOIN s USING (txn_ref)
Otherwise fan-out shows up as mismatches that are not real.
Two anti-joins plus a comparisonworks
-- missing: NOT EXISTS both ways
-- mismatched: INNER JOIN WHERE amounts differ
Correct, three passes over the data, three places to keep in sync.
INNER JOIN and compareavoid
SELECT * FROM ledger l JOIN settlement s USING (txn_ref)
WHERE l.amount <> s.amount
Finds mismatches among matched rows and cannot see anything missing.
The answer most people give
"Compare the two totals — if they match, we reconcile." Two sets of errors that cancel produce identical totals, and a missing 500 alongside an extra 500 is a clean bill of health. Reconciliation is per-key or it is not reconciliation.
They’ll ask next
The totals match exactly but 40 rows are classified as mismatched. How is that possible?
EvergreenDate-time & sessionizationWindow functionsGROUP BY semantics
Write the query for Day 1, Day 7 and Day 30 retention by signup cohort. What are the decisions you have to make before you can write it?
Why they ask this
Retention is the metric every product company asks about, and the query is easy — the definition is not, which is what the question is really probing.
Say this
Anchor each user to their signup date, then check for activity on the offset day. The decisions are whether Day 7 means exactly day 7 or within 7 days, and whether cohorts with incomplete windows are excluded.
The reasoning
**Decide what "Day 7 retained" means first.** *Exact-day* retention asks whether the user was active on the seventh day specifically — a strict measure that produces low, spiky numbers. *Bounded* retention asks whether they were active on or before day 7 — a much higher number and a different metric. Both are used in the wild, and the query differs by one operator. An interviewer asking this wants you to name the ambiguity rather than pick silently.
**Anchor the cohort.** Each user has one signup date; that date is their cohort and the origin for every offset. The join is activity to user on `user_id`, with the offset computed as the difference between the activity date and the signup date. Counting distinct users, not events, is the part that goes wrong when someone joins to a raw event table.
**Exclude incomplete cohorts.** A cohort that signed up three days ago cannot have Day 7 retention — but a naive query returns 0% for it rather than null, and that zero drags the average down and makes retention look like it is collapsing. Filter to cohorts where `signup_date + 30 days <= current_date` for the Day 30 column, or return null explicitly. This is the single most common bug in a retention query.
**Watch the timezone and the boundary.** "Day 1" computed in UTC against activity timestamped in local time shifts users between buckets. Cast both sides to the same timezone's date before subtracting, and say which timezone the metric is defined in.
The formulations
Exact-day retentionship
SELECT u.signup_date AS cohort,
COUNT(DISTINCT u.user_id) AS cohort_size,
COUNT(DISTINCT CASE WHEN DATE_DIFF(a.activity_date, u.signup_date, DAY) = 7
THEN u.user_id END) AS d7
FROM users u LEFT JOIN activity a USING (user_id)
WHERE u.signup_date <= CURRENT_DATE - 30
GROUP BY 1
Strict definition, and incomplete cohorts excluded.
Bounded (within N days)ship
... WHEN DATE_DIFF(a.activity_date, u.signup_date, DAY)
BETWEEN 1 AND 7 THEN u.user_id END
A different, higher metric. Say which one you are reporting.
No cohort completeness filteravoid
-- includes users who signed up yesterday
-- their D30 is 0%, not null
Recent cohorts report 0% and drag the whole trend down.
COUNT(*) instead of COUNT(DISTINCT user_id)avoid
COUNT(CASE WHEN ... THEN 1 END)
Counts events. A user active five times on day 7 counts as five.
The answer most people give
"Count users who came back, divided by users who signed up." That is the shape, and without the offset definition and the completeness filter it produces a number that changes meaning depending on when you run it. The definition is the hard part; the SQL is not.
They’ll ask next
Retention has been flat for a year and then drops 20% in the last month. What would you check before believing it?
EvergreenWindow functionsDate-time & sessionizationJoins & set ops
Find the users whose next purchase after buying an iPhone was AirPods. How do you express "immediately after", and what breaks if you get it wrong?
Why they ask this
Sequence questions are a staple, and the word "immediately" is doing all the work — candidates who reach for a self-join usually answer a different question than the one asked.
Say this
Order each user's purchases and use LEAD to look at the next one. "Immediately after" means the adjacent row in that ordering, which a self-join on "later than" cannot express without extra work.
The reasoning
**LEAD is the direct expression.** Partition by user, order by purchase time, and `LEAD(product)` gives you the next product that user bought. Filtering to rows where the current product is the iPhone and the lead is AirPods answers exactly the question asked, in one pass, with no join at all.
**Why a self-join is the wrong instinct.** Joining purchases to itself on `user_id` with `b.ts > a.ts` finds every AirPods purchase that happened *at any point after* an iPhone — including six months and four purchases later. To make it mean "immediately", you need a `NOT EXISTS` for any purchase in between, and now you have written three times the SQL for a worse plan.
**The tie is the trap.** Two purchases with the same timestamp — the same order, or a coarse timestamp column — make `LEAD` non-deterministic: either could be "next". Add a deterministic tiebreaker to the `ORDER BY`, usually an id, or the same query returns different answers on different runs and nobody can reproduce the bug.
**Say what the window is.** "Immediately after" with no time bound counts a purchase two years later as the next one. If the business means "in the same session" or "within 30 days", that has to be an explicit condition on the time difference — and it is worth asking, because the answer changes the number substantially.
The formulations
LEAD over the usership
WITH seq AS (
SELECT user_id, product, purchased_at,
LEAD(product) OVER (PARTITION BY user_id
ORDER BY purchased_at, purchase_id) AS next_product
FROM purchases)
SELECT DISTINCT user_id FROM seq
WHERE product = 'iPhone' AND next_product = 'AirPods'
One pass, exactly "next", and the id breaks timestamp ties.
LEAD with a time boundship
... AND LEAD(purchased_at) OVER (...) < purchased_at + INTERVAL '30 days'
When "immediately" has a business meaning, make it explicit.
Self-join plus NOT EXISTSworks
JOIN purchases b ON b.user_id = a.user_id AND b.purchased_at > a.purchased_at
WHERE NOT EXISTS (SELECT 1 FROM purchases m
WHERE m.user_id = a.user_id
AND m.purchased_at > a.purchased_at
AND m.purchased_at < b.purchased_at)
Correct and three times the SQL for a worse plan.
Self-join on "later than"avoid
JOIN purchases b ON b.user_id = a.user_id AND b.purchased_at > a.purchased_at
WHERE a.product = 'iPhone' AND b.product = 'AirPods'
Answers "ever after", not "immediately after". Different number.
The answer most people give
"Join the table to itself where the second purchase is later." That finds anyone who ever bought AirPods after an iPhone, which is a much larger set. The word "immediately" means adjacency in an ordering, and adjacency is what window functions are for.
They’ll ask next
Two purchases share a timestamp to the second. Which one does LEAD return?
Find every account with 3 or more failed withdrawals inside any 10-minute window. Why is a GROUP BY on the minute wrong?
Why they ask this
It is the fraud and alerting pattern, and the tempting wrong answer — bucketing by a fixed interval — is wrong in a way that is easy to state and easy to miss.
Say this
Fixed buckets miss bursts that straddle a boundary. Use a window ordered by time: compare each failure to the third-previous one with LAG, or count within a RANGE frame that follows the row.
The reasoning
**Why fixed buckets fail.** Truncating to a ten-minute bucket and counting puts 10:09 and 10:11 in different groups, so three failures at 10:09, 10:10 and 10:11 are never seen together. The window the business means is *rolling* — any ten consecutive minutes — and a `GROUP BY` on a truncated timestamp cannot express that. It will find some of the bursts and silently miss the ones near a boundary, which is the worst kind of wrong for an alerting rule.
**The LAG formulation is the cleanest.** Order each account's failures by time and look back two rows: if the current failure and the one two positions earlier are within ten minutes, then those two plus everything between them are three failures inside ten minutes. It generalises — for N events, look back N-1 rows — and it is a single pass.
**The RANGE frame formulation** is the direct translation of the sentence: count rows in a window defined by a time interval rather than a row offset. `COUNT(*) OVER (PARTITION BY account_id ORDER BY event_time RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW)` gives, for each failure, how many happened in the preceding ten minutes. Support for interval `RANGE` frames varies by engine, which is worth knowing before you write it on a whiteboard.
**Two details that decide correctness.** Filter to failures *before* windowing, or successful withdrawals count toward the burst. And be explicit about whether the window is inclusive at both ends — with alerting thresholds, "3 within 10 minutes" and "more than 3 within 10 minutes" are different rules and someone has to decide which fires.
The formulations
LAG N-1 rows backship
WITH f AS (SELECT account_id, event_time,
LAG(event_time, 2) OVER (PARTITION BY account_id
ORDER BY event_time) AS two_ago
FROM withdrawals WHERE status = 'failed')
SELECT DISTINCT account_id FROM f
WHERE two_ago >= event_time - INTERVAL '10 minutes'
One pass, generalises to any N, works on every engine.
RANGE frame over an intervalship
COUNT(*) OVER (PARTITION BY account_id ORDER BY event_time
RANGE BETWEEN INTERVAL '10 minutes' PRECEDING AND CURRENT ROW) >= 3
Reads exactly like the requirement. Engine support varies.
Self-join within the intervalworks
JOIN withdrawals b ON b.account_id = a.account_id
AND b.event_time BETWEEN a.event_time AND a.event_time + INTERVAL '10 minutes'
GROUP BY a.account_id, a.event_time HAVING COUNT(*) >= 3
Correct and quadratic on a busy account.
GROUP BY a truncated timestampavoid
GROUP BY account_id, DATE_TRUNC('minute', event_time) -- bucketed
HAVING COUNT(*) >= 3
Misses every burst that crosses a bucket boundary.
The answer most people give
"Group by account and ten-minute bucket, then filter on count >= 3." It looks right and it under-reports: a burst spanning 10:09 to 10:11 is split across two buckets and never triggers. For an alerting rule that is a silent false negative.
They’ll ask next
The rule fires and you need the three specific transactions that triggered it. Does your query still work?
Compute the median order value. Your engine has no MEDIAN function. Which formulation do you ship?
Why they ask this
It is asked because at least one engine every team uses is missing the function, and because the even-row-count case separates people who have written it from people who have read about it.
Say this
Rank the rows and take the middle position or positions. `rn IN ((n+1)/2, (n+2)/2)` with integer division covers odd and even counts in one expression, and averaging the survivors is a no-op when there is only one.
The reasoning
**The definition first.** The median is the value at the middle position of the sorted rows. With an odd count there is one middle row. With an even count there are two, and the median is their average — which is why a median can be a value no row actually holds.
**The trick that removes the branch.** Take the rows at positions `(n + 1) / 2` and `(n + 2) / 2` using integer division. For n = 9 both evaluate to 5, so you select one row and average it with itself. For n = 8 they evaluate to 4 and 5, so you select the two straddling rows and average them. One expression, no CASE, both cases correct.
**Where the window has to live.** `ROW_NUMBER()` and `COUNT(*) OVER ()` are evaluated after WHERE, so you cannot filter on `rn` in the same SELECT that computes it. It has to be a CTE or a subquery, and forgetting that is the most common compile error on this question.
**Per group it is the same query with a PARTITION BY** on both windows. Watch what you report alongside it: after the filter only one or two rows per group remain, so `COUNT(*)` reports 1 or 2 rather than the group size. Carry the size with `MAX(n)`.
**And say the portable line out loud:** on PostgreSQL or Snowflake this is `PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x)` and you would ship that instead. Knowing the manual version is what lets you answer on the engine that lacks it.
The formulations
ROW_NUMBER, both middle positionsship
WITH ranked AS (
SELECT amount,
ROW_NUMBER() OVER (ORDER BY amount) AS rn,
COUNT(*) OVER () AS n
FROM orders
)
SELECT AVG(amount) FROM ranked
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
Correct for odd and even counts with no branch. Portable to any engine with window functions.
PERCENTILE_CONT(0.5)ship
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount)
FROM orders
What you write where it exists. Interpolates, which for the median is exactly the definition.
LIMIT 1 OFFSET n/2avoid
SELECT amount FROM orders
ORDER BY amount
LIMIT 1 OFFSET (SELECT COUNT(*) / 2 FROM orders)
Silently wrong on an even count — it returns one of the two middle rows instead of their average.
AVG of MIN and MAXavoid
SELECT (MIN(amount) + MAX(amount)) / 2 FROM orders
That is the midrange, not the median. One outlier moves it and it is not a percentile of anything.
The answer most people give
`ORDER BY amount LIMIT 1 OFFSET count/2`. It looks right, runs, and returns a plausible number — and it is wrong on every even row count, which is half the datasets you will meet.
They’ll ask next
Now the median per city, with the city sizes reported alongside. What breaks?
A feed is paginated with LIMIT 20 OFFSET 17980 and page 900 takes four seconds. Rewrite it.
Why they ask this
It separates people who know OFFSET is a position from people who assume the database skips rows for free.
Say this
Replace the offset with a cursor: the client sends back the last row it displayed, and the WHERE clause starts strictly after it. Cost stops depending on depth, and the predicate must mirror the sort keys exactly.
The reasoning
**Why it is slow.** OFFSET does not seek. The engine produces the 17,980 rows it is told to skip, discards them, and then returns twenty — so the work grows linearly with the page number, and the user experience gets worse the more engaged the user is.
**The rewrite.** Keep the ordering, drop the offset, and add a predicate naming the last row shown: `WHERE (created_at, id) < (:last_at, :last_id)`. With an index on `(created_at, id)` the engine seeks straight to that point. Page 900 now costs what page 2 costs.
**The predicate has to match the ordering exactly.** Comparing on `created_at` alone skips every row tying with the cursor row; comparing on `id` alone returns rows from the wrong point in the sort. And the comparison must be strict — `<=` returns the cursor row itself, which then appears at the bottom of one page and the top of the next.
**Where row comparison is unsupported,** spell it out as `created_at < :last_at OR (created_at = :last_at AND id < :last_id)`. It is the same predicate; some engines optimise the tuple form better, so check the plan rather than assuming.
**What you lose** is jumping to an arbitrary page number, which a feed does not need and an admin table does. If both are required, keep OFFSET for the page picker and use the cursor for scroll.
The formulations
Keyset cursor on the full sort keyship
SELECT id, created_at, body
FROM posts
WHERE (created_at, id) < (:last_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20
Flat cost at any depth, stable under concurrent inserts. Needs an index on (created_at, id).
Cursor on the timestamp onlyavoid
WHERE created_at < :last_at
ORDER BY created_at DESC, id DESC
LIMIT 20
Silently skips every row sharing the cursor timestamp. Invisible until two rows share a second.
OFFSET with a covering indexworks
SELECT id, created_at, body FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20 OFFSET 17980
An index makes each skipped row cheaper; it does not stop them being produced. Better, still linear.
Cache the page numbersavoid
-- materialise page -> id ranges nightly
Adds a staleness problem and a job to own, to avoid a predicate you could have written.
The answer most people give
"Add an index and it will be fine." An index reduces the cost per skipped row but the engine still walks all 17,980 of them — the growth is linear either way.
They’ll ask next
The feed also lets users jump to a date. Does the cursor still work?
