A query, a handful of rows, and one question: what comes back? Every one is drawn from a place where the obvious answer is wrong — and every published result here was produced by running the query, not by remembering what it should do.
Where three-valued logic quietly changes the row count or the average.
Joins & row counts
4
Predict the number of rows before you predict their contents.
Grouping & aggregates
4
Empty sets, NULL groups, and the difference between zero and nothing.
Windows & ordering
4
Frames, partitions and the order things are evaluated in.
DML & transactions
4
What the table looks like afterwards — including when the answer is "it depends".
Evergreen · asked verbatim
2
Questions asked in these words, often enough to be worth a prepared answer.
01 / 22
NULL logicGROUP BY semantics
Three rows, one of them NULL. What do AVG(v), SUM(v)/COUNT(*) and AVG(COALESCE(v,0)) return?
Given these rows
t
v
10
20
NULL
The query — predict the output before reading on
SELECT AVG(v) AS avg_v,
SUM(v) * 1.0 / COUNT(*) AS sum_over_count,
AVG(COALESCE(v, 0)) AS avg_coalesced
FROM t;
Why they ask this
Three expressions that all look like "the average" and return three different numbers. Which one is right is a business question, not a SQL question.
Say this
AVG(v) is 15 — it divides by the 2 non-NULL rows. SUM(v)/COUNT(*) is 10, because COUNT(*) counts all 3. AVG(COALESCE(v,0)) is also 10, because the NULL became a real zero.
The reasoning
Aggregates ignore NULLs. `AVG(v)` is `SUM(v) / COUNT(v)`, and `COUNT(v)` excludes the NULL, so the denominator is 2 and the answer is 15. That is the standard behaviour and it is usually what you want: the average of the values you have.
The other two change the denominator to 3, which asserts something different — that the missing value is a measured zero. For a sensor reading that failed to arrive, that is wrong and it drags every average toward zero. For a count of purchases where "no row" genuinely means "none", it is right.
So the interview answer is not the number, it is: which denominator does the business want? Say it out loud and pick deliberately. The failure mode in real reporting is that different dashboards pick differently and nobody notices until two numbers disagree.
What it actually returns verified against SQLite
avg_v
sum_over_count
avg_coalesced
15
10
10
The answer most people give
"They are all 15" or "they are all 10." The trap is assuming NULL handling is consistent across the three — it is not, and the difference is 50% on this data.
They’ll ask next
A sensor that failed to report versus a sensor that reported zero. Which expression do you want for each?
ORDER BY on a column containing NULL — do the NULLs come first or last?
Given these rows
t
v
2
NULL
1
The query — predict the output before reading on
SELECT 'ASC' AS dir, COALESCE(CAST(v AS TEXT),'NULL') AS v, ROW_NUMBER() OVER (ORDER BY v) AS pos FROM t
UNION ALL
SELECT 'DESC', COALESCE(CAST(v AS TEXT),'NULL'), ROW_NUMBER() OVER (ORDER BY v DESC) FROM t
ORDER BY dir, pos;
Why they ask this
It is genuinely unspecified by the standard, so the honest answer is "it depends, and here is how I would stop it depending".
Say this
It is engine-dependent. SQLite and PostgreSQL treat NULL as smaller than everything, so ascending puts NULLs first — but PostgreSQL defaults to NULLs *last* on ASC. Oracle defaults the other way again. The fix is to say `NULLS FIRST` or `NULLS LAST` explicitly.
The reasoning
The SQL standard leaves NULL ordering implementation-defined and only requires that all NULLs sort together. Engines then chose differently: PostgreSQL and Oracle treat NULL as larger than everything (so ASC puts them last), while SQLite and MySQL treat them as smaller (ASC puts them first). The output below is SQLite's.
That means a query with a `LIMIT` and no explicit NULL handling can return a completely different top row on two engines with identical data — and the difference shows up as a wrong dashboard rather than an error.
The portable fix is `ORDER BY v ASC NULLS LAST` where the dialect supports it, or `ORDER BY (v IS NULL), v` where it does not — the boolean sorts false before true, pushing NULLs to the end. Either way, the intent is written down instead of inherited.
What it actually returns verified against SQLite
SQLite: NULL sorts before every value ascending, and last descending.
dir
v
pos
ASC
NULL
1
ASC
1
2
ASC
2
3
DESC
2
1
DESC
1
2
DESC
NULL
3
The answer most people give
"NULLs always sort last." That is PostgreSQL's ASC default and it is not universal — SQLite, shown here, does the opposite. Asserting either as a rule is how a query behaves differently in the warehouse than it did locally.
They’ll ask next
Your dialect has no NULLS LAST. Write the ORDER BY that does the same thing.
What does each of these return when the denominator is zero?
Given these rows
t
n
d
10
0
10
2
The query — predict the output before reading on
SELECT n, d,
n * 1.0 / NULLIF(d, 0) AS guarded,
COALESCE(n * 1.0 / NULLIF(d, 0), 0) AS defaulted,
CASE WHEN d = 0 THEN NULL ELSE n * 1.0 / d END AS explicit
FROM t;
Why they ask this
NULLIF is the idiom for this and a lot of people have never seen it. It also sets up the more interesting question of whether NULL or 0 is the right answer.
Say this
NULLIF(d, 0) turns the zero denominator into NULL, so the division yields NULL instead of raising. All three expressions agree; the difference is only whether you then coalesce that NULL to zero.
The reasoning
`NULLIF(a, b)` returns NULL when the two arguments are equal and `a` otherwise. Using it on a denominator converts the error case into a missing value, which is almost always what a report wants — a rate with no denominator is genuinely unknown rather than zero.
Note that SQLite does not raise on division by zero at all; it returns NULL. PostgreSQL, SQL Server and most warehouses do raise. So this is one of the cases where the guard is not optional on the engine you will actually ship to, even though the query appears to work locally.
The `COALESCE(..., 0)` version is a decision, not a formality. A conversion rate of 0% and an unknown conversion rate are different facts, and averaging a column where the unknowns were coerced to zero drags the average down by exactly the number of unknowns.
What it actually returns verified against SQLite
n
d
guarded
defaulted
explicit
10
0
NULL
0
NULL
10
2
5
5
5
The answer most people give
"It throws a divide-by-zero error." Not with the NULLIF guard, and not in SQLite at all — which is exactly why testing this locally tells you nothing about what the warehouse will do.
They’ll ask next
This column feeds an average across a thousand rows, forty of which have a zero denominator. Which version do you want and why?
What comes out when you concatenate a string with a NULL?
Given these rows
t
first
last
Ada
NULL
Grace
Hopper
The query — predict the output before reading on
SELECT first, last,
first || ' ' || last AS pipes,
COALESCE(first,'') || ' ' || COALESCE(last,'') AS guarded
FROM t;
Why they ask this
A one-character change between a name column that works and a name column that is silently empty for every customer missing a surname.
Say this
NULL propagates through `||`, so the whole concatenation becomes NULL — not "Ada ", and not "Ada NULL". One missing component erases the entire string.
The reasoning
The `||` operator follows the same rule as arithmetic: any NULL operand makes the whole expression NULL. So a display name built by concatenating first and last name disappears entirely for anyone with a missing surname, rather than degrading to the part you do have.
The behaviour is not universal. Standard `CONCAT()` in MySQL and several warehouses skips NULLs and returns the rest, and `CONCAT_WS` skips them and handles the separator too. That inconsistency is why the same expression can produce different output after a migration.
The portable fix is to coalesce each component, or to use `CONCAT_WS` where it exists. Worth noticing what the guarded version costs: "Ada " with a trailing space, because the separator is unconditional. If that matters, trim it — the point is that both failure modes are now visible rather than one being silent.
What it actually returns verified against SQLite
first
last
pipes
guarded
Ada
NULL
NULL
Ada
Grace
Hopper
Grace Hopper
Grace Hopper
The answer most people give
"It returns 'Ada ' with the NULL treated as an empty string." That is what CONCAT does in some engines and what `||` never does. The whole value is NULL.
They’ll ask next
The guarded version leaves a trailing space. Does that matter, and what would you do about it?
Two tables, both with a NULL in the join column. How many rows does the inner join return?
Given these rows
a
k
x
NULL
b
k
x
NULL
The query — predict the output before reading on
SELECT COUNT(*) AS inner_rows FROM a JOIN b ON a.k = b.k;
Why they ask this
People predict 2 by pattern-matching the shape of the data. The answer follows from one rule they can already state, which makes it a good test of whether they apply it.
Say this
One row. The two `x` values match; the two NULLs do not, because `NULL = NULL` is UNKNOWN and a join keeps only rows where the predicate is TRUE.
The reasoning
This is the same three-valued logic rule as everywhere else, applied to a join predicate. It surprises people because the two NULLs look identical sitting next to each other in the data — but the join is asking whether they are *equal*, and that question has no answer.
The consequence in a pipeline is a slow leak. Every fact row whose foreign key is NULL silently fails to join and vanishes from an inner join. Nothing errors, the report is just short by however many rows had a missing key — and that count usually grows over time as an upstream system gets sloppier.
If NULLs must match, say so explicitly: `IS NOT DISTINCT FROM` where the dialect has it, or `a.k = b.k OR (a.k IS NULL AND b.k IS NULL)`. Coalescing both sides to a sentinel works right up until the sentinel appears in real data.
What it actually returns verified against SQLite
Two rows on each side, one matching pair.
inner_rows
1
The answer most people give
"Two — the x matches the x and the NULL matches the NULL." NULLs never match in a join predicate. This is the single most common source of quietly missing rows in a warehouse.
They’ll ask next
How would you detect, in one query, how many fact rows are being lost to a NULL foreign key?
Two tables of three rows each, overlapping on one key. How many rows does a FULL OUTER JOIN return?
Given these rows
a
k
1
2
3
b
k
3
4
5
The query — predict the output before reading on
SELECT COUNT(*) AS rows_out,
SUM(CASE WHEN a.k IS NULL THEN 1 ELSE 0 END) AS only_in_b,
SUM(CASE WHEN b.k IS NULL THEN 1 ELSE 0 END) AS only_in_a
FROM a FULL OUTER JOIN b ON a.k = b.k;
Why they ask this
It checks whether someone can reason about a set operation rather than recite a definition, and the two NULL-side counts are how a reconciliation query is actually written.
Say this
Five: two rows only in A, two only in B, and one matched row that appears once rather than twice. A full outer join returns the union of the keys, not the sum of the row counts.
The reasoning
The arithmetic is: matched keys contribute one row each, and every unmatched key on either side contributes one row with NULLs on the other side. Here that is 1 matched + 2 + 2 = 5. The instinct to say 6 comes from adding the two table sizes, which is what `UNION ALL` does, not what a join does.
The two conditional counts are the useful part. `a.k IS NULL` identifies rows present only in B and `b.k IS NULL` identifies rows only in A — which turns a full outer join into a complete reconciliation between two systems in a single pass. That idiom is worth having ready.
One practical note: MySQL has no FULL OUTER JOIN, and the workaround is a `LEFT JOIN UNION ALL` with the right-only rows selected via an anti-join. Knowing that the workaround exists — and that a plain `UNION` of two left joins double-counts the matched rows — is the follow-up most people miss.
What it actually returns verified against SQLite
rows_out
only_in_b
only_in_a
5
2
2
The answer most people give
"Six — three plus three." That is the count for UNION ALL. The matched key appears once in a join, not once per side.
They’ll ask next
Your engine has no FULL OUTER JOIN. Write the equivalent, and say why a plain UNION of two LEFT JOINs is wrong.
A cross join between a three-row table and an empty one. And what does the aggregate over it return?
Given these rows
a
k
1
2
3
The query — predict the output before reading on
WITH empty(k) AS (SELECT 1 WHERE 0)
SELECT COUNT(*) AS rows_out, SUM(a.k) AS sum_k, COUNT(a.k) AS count_k
FROM a CROSS JOIN empty;
Why they ask this
Two things at once: multiplying by zero rows, and the difference between SUM and COUNT over an empty set — which is the more useful half.
Say this
Zero rows, because 3 × 0 = 0. The aggregate over that empty result returns COUNT = 0 but SUM = NULL, not 0.
The reasoning
The row count is straightforward once you remember a cross join is a product. The part worth internalising is what the aggregates do afterwards: `COUNT` over an empty set is defined as 0, while `SUM`, `AVG`, `MIN` and `MAX` over an empty set are all NULL.
That asymmetry causes real bugs. A daily total computed as `SUM(amount)` returns NULL on a day with no rows, and NULL then propagates through every downstream calculation — a running total that hits one empty day becomes NULL for the rest of the series unless something coalesces it.
It also means `SUM(x) = 0` and `SUM(x) IS NULL` are different conditions with different meanings: "measured and it summed to zero" against "there was nothing to measure". Reports that conflate them lose the ability to distinguish a quiet day from a broken pipeline.
What it actually returns verified against SQLite
rows_out
sum_k
count_k
0
NULL
0
The answer most people give
"SUM returns 0 over no rows." COUNT does; SUM returns NULL. It is the difference between a day with no sales and a day whose data never arrived.
They’ll ask next
A running total hits a day with no rows. What happens to the rest of the series?
Four rows self-joined on `a.id < b.id`. How many pairs come back, and why that number?
Given these rows
t
id
1
2
3
4
The query — predict the output before reading on
SELECT COUNT(*) AS pairs FROM t a JOIN t b ON a.id < b.id;
Why they ask this
It checks whether someone can predict the *size* of a join rather than just its shape — which is the skill that stops a query from producing a billion rows in production.
Say this
Six — every unordered pair of four items, which is 4 × 3 / 2. Using `<>` instead of `<` would return twelve, because each pair appears in both orders.
The reasoning
The `<` is doing two jobs: it excludes a row pairing with itself, and it picks exactly one of the two orderings for every pair. That makes it the idiom for "every combination" — and its output size is quadratic, which is the number to say out loud.
Quadratic is the whole point of the question. Four rows give six pairs; a thousand rows give roughly half a million; a hundred thousand rows give five billion. A self-join with an inequality is one of the few things in SQL that can turn a small table into an unfinishable query, and it usually gets written for something innocent like "find overlapping bookings".
When the pairs are only needed within a group — overlapping bookings for the *same* room — adding that equality to the join condition changes the cost from N² overall to the sum of the per-group squares, which is dramatically smaller. And where the real question is about adjacent rows rather than all pairs, `LAG` over an ordered window replaces the join entirely and runs in one pass.
What it actually returns verified against SQLite
pairs
6
The answer most people give
"Twelve" (forgetting that `<` picks one ordering) or "sixteen" (that is the unrestricted cross join). Both suggest the candidate is not thinking about the join's cardinality at all.
They’ll ask next
The real requirement is overlapping bookings in the same room. How does that change the cost?
GROUP BY semanticsNULL logicData quality assertions
Grouping a column that contains NULLs, with HAVING COUNT(*) > 1. Does the NULL group appear?
Given these rows
t
k
a
a
NULL
NULL
b
The query — predict the output before reading on
SELECT COALESCE(k, '<null>') AS k, COUNT(*) AS n
FROM t GROUP BY k HAVING COUNT(*) > 1 ORDER BY k;
Why they ask this
It combines two rules people know separately — NULLs group together, aggregates ignore NULLs — and most people apply the wrong one.
Say this
Yes. GROUP BY puts the two NULLs in one group, and `COUNT(*)` counts rows rather than values, so that group has a count of 2 and passes the HAVING.
The reasoning
The two rules do not conflict, they apply to different things. Grouping uses distinctness, under which NULLs are not distinct from each other — so all the NULL rows land in one group. `COUNT(*)` then counts the rows in that group without looking at any value, so the NULL-ness is irrelevant.
Swap in `COUNT(k)` and the answer changes completely: that counts non-NULL values, which is 0 for the NULL group, so it fails `> 1` and disappears. Two characters, opposite results.
This exact pattern is how duplicate-key checks are written, which is why it matters. `GROUP BY key HAVING COUNT(*) > 1` correctly reports that you have two rows with a missing key — usually something you want to know. The `COUNT(key)` version silently ignores them, and the missing keys are precisely the rows most likely to be broken.
What it actually returns verified against SQLite
k
n
<null>
2
a
2
The answer most people give
"No — NULLs are ignored by aggregates so the group cannot have a count." Aggregates ignore NULL *values*; COUNT(*) never looks at a value. The group is formed by GROUP BY, which does put the NULLs together.
They’ll ask next
Change COUNT(*) to COUNT(k). What comes back now, and which version do you want in a duplicate-key check?
A customer with no orders, after a LEFT JOIN. What do COUNT(*) and COUNT(o.id) report for them?
Given these rows
c
id
1
2
o
id
customer_id
10
1
The query — predict the output before reading on
SELECT c.id, COUNT(*) AS count_star, COUNT(o.id) AS count_orders
FROM c LEFT JOIN o ON o.customer_id = c.id
GROUP BY c.id ORDER BY c.id;
Why they ask this
The single most common off-by-one in reporting SQL: a customer with no orders reported as having one.
Say this
COUNT(*) says 1 and COUNT(o.id) says 0. The LEFT JOIN produces a row for the unmatched customer with NULL order columns, so there is a row to count but no order to count.
The reasoning
This is the practical payoff of knowing what the two counts do. The LEFT JOIN guarantees one output row per customer even when nothing matched — that is its job — so `COUNT(*)` is counting the join output, not the orders. Every customer gets at least 1.
`COUNT(o.id)` counts non-NULL values of a column that comes from the right side, and for the unmatched customer that value is NULL. So it correctly reports zero. The rule to carry away: after an outer join, always count a column from the *optional* side, never `*`.
The same trap applies to `SUM`. `SUM(o.amount)` returns NULL rather than 0 for the customer with no orders, which is arguably more honest than a zero but will surprise anything that adds it up. Coalescing is a decision to make deliberately.
What it actually returns verified against SQLite
id
count_star
count_orders
1
1
1
2
1
0
The answer most people give
"Both return 0 for the customer with no orders." COUNT(*) returns 1, because the LEFT JOIN deliberately produced a row for them. That 1 is the bug that puts every dormant customer in the "has ordered" bucket.
They’ll ask next
What does SUM(o.amount) return for customer 2, and is that the number you want in a report?
Does GROUP BY 1 group by the first select expression or by the literal number one?
Given these rows
t
k
v
a
1
a
2
b
3
The query — predict the output before reading on
SELECT k, COUNT(*) AS n FROM t GROUP BY 1 ORDER BY 1;
Why they ask this
It looks like trivia and is a real portability hazard — the same syntax means different things in ORDER BY and in GROUP BY across engines.
Say this
By the first select expression. An integer literal in GROUP BY or ORDER BY is a positional reference, not a constant — so this groups by `k` and returns two rows.
The reasoning
Positional references are standard in `ORDER BY` and widely supported in `GROUP BY` (PostgreSQL, MySQL, SQLite, most warehouses). They are concise, and for a query grouping by three long expressions they genuinely improve readability.
The hazard is that the position refers to the *select list*, so inserting a column at the front of the SELECT silently changes what the query groups by. Nothing errors — the query just returns different numbers, and diffs of that change look innocuous in review.
Some engines also let you `GROUP BY` a select alias, which is the readable middle ground: it survives reordering and still avoids repeating the expression. It is not portable everywhere, so the safe default in shared code is to repeat the expression and accept the verbosity.
What it actually returns verified against SQLite
k
n
a
2
b
1
The answer most people give
"It groups everything into one bucket because 1 is a constant." That is what `GROUP BY (SELECT 1)` or `GROUP BY '1'` would do in some engines. A bare integer is a position.
They’ll ask next
Someone adds a column to the front of the SELECT list. What happens, and would a code review catch it?
Does SELECT DISTINCT a, b deduplicate each column separately or the pair?
Given these rows
t
a
b
x
1
x
2
y
1
The query — predict the output before reading on
SELECT COUNT(*) AS distinct_pairs FROM (SELECT DISTINCT a, b FROM t);
Why they ask this
A surprising number of people believe DISTINCT applies to the column it sits next to. The answer decides whether a dedup query is correct or nonsense.
Say this
The pair. DISTINCT applies to the entire select list as one tuple, so all three rows survive — there are three distinct `(a, b)` combinations even though `a` has only two values.
The reasoning
DISTINCT is a property of the row, not of a column. `SELECT DISTINCT a, b` returns the distinct combinations, which is why it deduplicates less aggressively than people expect when the select list is wide — and why `SELECT DISTINCT *` almost never deduplicates anything useful.
The related trap is `COUNT(DISTINCT a, b)`. MySQL accepts it and counts distinct pairs; PostgreSQL requires `COUNT(DISTINCT (a, b))` with the row constructor; several engines reject it entirely. Where it is unsupported, the portable form is a `COUNT(*)` over a `SELECT DISTINCT` subquery, which is what this snippet does.
If the intent really was "distinct values of a", the query is `SELECT DISTINCT a` — and if you need both independently, that is two separate aggregates, not one DISTINCT.
What it actually returns verified against SQLite
distinct_pairs
3
The answer most people give
"Two, because a only has two distinct values." That would be `SELECT DISTINCT a`. Adding a column to a DISTINCT select list can only ever increase the number of rows returned.
They’ll ask next
Write a portable count of distinct (a, b) pairs, without relying on COUNT(DISTINCT a, b).
Does a WHERE clause filter the rows a window function sees?
Given these rows
t
k
v
a
1
a
2
b
5
The query — predict the output before reading on
SELECT k, v, SUM(v) OVER () AS total_seen
FROM t WHERE k = 'a';
Why they ask this
It settles whether a candidate really knows the processing order or has only memorised that "windows come late".
Say this
Yes. WHERE runs before window functions, so the window only sees the surviving rows — the total is 3, not 8. That is why filtering *on* a window result needs QUALIFY or a subquery.
The reasoning
The order is FROM, WHERE, GROUP BY, HAVING, then window functions, then SELECT, then ORDER BY. So a WHERE clause shrinks the input the window operates over, and the "total" here is the total of the filtered set.
This is usually what you want and occasionally exactly what you do not. If you need each row's share of the *unfiltered* total, the filter has to be applied after the window — typically by computing the window in a subquery and filtering outside it, or by using a conditional aggregate so the excluded rows still contribute to the denominator.
The mirror image is why you cannot write `WHERE ROW_NUMBER() OVER (...) = 1`. At the time WHERE is evaluated the window has not been computed, so the name does not exist yet. QUALIFY exists to fill that gap in the dialects that have it; elsewhere you wrap and filter outside.
What it actually returns verified against SQLite
k
v
total_seen
a
1
3
a
2
3
The answer most people give
"The window sees all the rows because it runs over the whole table." It runs over whatever survived WHERE. If you want the unfiltered total you have to arrange for it explicitly.
They’ll ask next
I want each row of group A alongside the total across every group. How do you write that?
What does LAG return for the first row of each partition, and does it reach into the previous partition?
Given these rows
t
k
v
a
1
a
2
b
9
The query — predict the output before reading on
SELECT k, v,
LAG(v) OVER (PARTITION BY k ORDER BY v) AS prev,
LAG(v, 1, 0) OVER (PARTITION BY k ORDER BY v) AS prev_default,
v - LAG(v) OVER (PARTITION BY k ORDER BY v) AS delta
FROM t ORDER BY k, v;
Why they ask this
The NULL at each partition boundary is what turns a day-over-day delta column into NULLs at exactly the rows a reader looks at first.
Say this
NULL — a partition never sees the one before it, which is the point of partitioning. The optional third argument to LAG supplies a default instead, and any arithmetic on an un-defaulted LAG yields NULL for that first row.
The reasoning
PARTITION BY resets the window at every group boundary, so the first row of each partition has no predecessor and `LAG` returns NULL. That is correct behaviour: the previous partition belongs to a different entity and reaching into it would be a bug.
The three-argument form `LAG(v, 1, 0)` supplies a default for exactly that case. Whether 0 is the right default is a real decision — for a day-over-day delta it implies the previous value was zero, which overstates the first delta. Often NULL is the honest answer and the report should show a blank.
The knock-on effect is the one to watch. `v - LAG(v)` is NULL for the first row of every partition, so a "change since yesterday" column has a hole per entity, and any downstream `SUM` of that column silently skips those rows.
What it actually returns verified against SQLite
k
v
prev
prev_default
delta
a
1
NULL
0
NULL
a
2
1
1
1
b
9
NULL
0
NULL
The answer most people give
"It returns the last row of the previous partition." That is what happens with no PARTITION BY at all — which is a different query and a genuine bug when the rows belong to different entities.
They’ll ask next
Your delta column is NULL for the first row of every partition. Is defaulting it to zero the right fix?
PARTITION BY a column containing NULLs — do the NULL rows form one partition or one each?
Given these rows
t
k
v
a
1
NULL
2
NULL
3
The query — predict the output before reading on
SELECT COALESCE(k,'<null>') AS k, v,
COUNT(*) OVER (PARTITION BY k) AS partition_size
FROM t ORDER BY k NULLS FIRST, v;
Why they ask this
It is the same distinctness-versus-equality question as GROUP BY, asked where people have not thought about it before.
Say this
One partition. Window partitioning uses the same not-distinct-from rule as GROUP BY, so all NULL rows land together — here giving a partition size of 2.
The reasoning
Consistency is the useful takeaway: GROUP BY, DISTINCT, set operations and window partitioning all use distinctness, under which NULLs are the same. Only comparison — `=` in a WHERE or ON clause — uses equality, where NULLs never match.
Once you have that one sentence, a whole family of questions collapses into it, and you can answer the ones you have not seen before. That is precisely what an interviewer is checking with a question like this.
The practical caution is that a large NULL partition is usually a data-quality signal rather than a legitimate group. A `COUNT(*) OVER (PARTITION BY customer_id)` returning a huge number for the NULL partition means a lot of rows lost their foreign key, and it is worth surfacing rather than aggregating over.
What it actually returns verified against SQLite
k
v
partition_size
<null>
2
2
<null>
3
2
a
1
1
The answer most people give
"Each NULL is its own partition because NULL is not equal to NULL." Equality is not what partitioning uses. It uses the same grouping rule as GROUP BY.
They’ll ask next
What would a very large NULL partition tell you about the upstream data?
ROW_NUMBER ordered by a column with duplicate values — which tied row gets number 1?
Given these rows
t
id
score
a
10
b
10
c
5
The query — predict the output before reading on
SELECT id, score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS unstable,
ROW_NUMBER() OVER (ORDER BY score DESC, id) AS stable
FROM t ORDER BY id;
Why they ask this
The output looks deterministic and is not. A dedup built on it can keep a different row on each run, and nothing in the query says so.
Say this
Unspecified. The engine may return either tied row as number 1, and it can differ between runs, plans or engine versions. Adding a unique tiebreaker to the ORDER BY is what makes it reproducible.
The reasoning
With `ORDER BY score DESC` alone, rows `a` and `b` are peers and the standard says nothing about which gets 1. In practice it usually follows whatever order the scan produced, which changes with the plan — so adding an index, upgrading the engine or partitioning the table can silently change the answer.
On this small input SQLite happens to return `a` first, and that stability is exactly what makes the bug hard to catch: it looks deterministic in every test you write. The `stable` column adds `id` to the ordering, which makes the assignment total and reproducible by construction.
This matters most for deduplication, where ROW_NUMBER decides which row survives. A non-deterministic dedup means two runs over identical input produce different tables, and every diff-based check downstream reports spurious changes.
What it actually returns verified against SQLite
The unstable column happens to pick `a` here — which is precisely why the bug survives testing.
id
score
unstable
stable
a
10
1
1
b
10
2
2
c
5
3
3
The answer most people give
"Whichever comes first in the table." Tables have no inherent order, and the row the scan happens to produce first depends on the plan, the storage layout and the engine version.
They’ll ask next
Your nightly dedup keeps a different row on some runs. Where do you look first?
An UPDATE … FROM where two source rows match one target row. Which value wins?
Given these rows
target
id
val
1
original
source
id
val
1
first
1
second
The query — predict the output before reading on
UPDATE target SET val = source.val FROM source WHERE source.id = target.id;
SELECT id, val FROM target;
Why they ask this
The answer is "one of them, arbitrarily, and no warning" — which almost nobody expects, and which corrupts a dimension load in a way that looks like a data problem rather than a query problem.
Say this
One of them, chosen arbitrarily by the engine. The target row is updated once, not twice, and nothing tells you the other value existed. SQLite returns `second` here; the choice is not guaranteed.
The reasoning
This is the most dangerous behaviour in this whole bank, because it is silent and it corrupts rather than fails. PostgreSQL and SQLite both pick a source row arbitrarily. SQL Server does the same. Only `MERGE` on several engines raises an error for a multi-match, which is why MERGE is the safer statement for an unattended job.
Note what does *not* happen: the target is not updated twice, and the row does not appear twice. There is exactly one target row and it ends up with exactly one value. So no row count, no error and no log line reveals that the source was ambiguous.
The defence is to make the source unique before the update — deduplicate to one row per key with a stated survivorship rule — and ideally to assert it, with a check that the source has no duplicate keys before the write runs. That assertion is cheap and it is the only thing standing between an ambiguous source and a silently wrong dimension.
What it actually returns verified against SQLite
SQLite picked `second` here. Another engine, or another plan, may pick `first` — nothing in the statement decides it.
id
val
1
second
The answer most people give
"It errors" or "it applies both, so the last one wins." Neither. It applies one, chosen arbitrarily, and reports success — which is why this is worth knowing before it happens to a dimension table.
They’ll ask next
How would you make this fail loudly instead of picking silently?
A DELETE whose subquery reads the same table it is deleting from. Does it see the rows already deleted?
Given these rows
t
id
grp
1
a
2
a
3
b
The query — predict the output before reading on
DELETE FROM t WHERE id NOT IN (SELECT MIN(id) FROM t GROUP BY grp);
SELECT id, grp FROM t ORDER BY id;
Why they ask this
It tests whether someone knows a statement sees a consistent snapshot rather than its own partial effects — which is what makes this dedup idiom safe at all.
Say this
No. The statement evaluates against a consistent snapshot taken before it began, so the subquery sees all three original rows. Rows 1 and 3 survive as the minimum id per group.
The reasoning
A single SQL statement is atomic with respect to itself: the subquery is evaluated against the table as it was before the DELETE started, not against a table that is shrinking as rows are removed. Without that guarantee this idiom would be unpredictable, because the result would depend on the order rows were visited.
That is what makes "delete all but the minimum id per group" a safe and common way to dedup a table in place. It is worth being able to say *why* it is safe rather than just that it works.
The guarantee is per statement, not per transaction. Two separate statements in one transaction each see the effects of the earlier one, so splitting this into a SELECT and then a DELETE — with anything happening in between — reintroduces exactly the race the single statement avoids.
What it actually returns verified against SQLite
id
grp
1
a
3
b
The answer most people give
"It deletes everything, because once row 1 is gone the minimum changes." That would be true if the statement saw its own effects. It does not — the snapshot is fixed before the delete begins.
They’ll ask next
Does the same guarantee hold if you split this into a SELECT and a DELETE inside one transaction?
In INSERT … ON CONFLICT DO UPDATE, what does `excluded.amount` mean — the existing row or the new one?
Given these rows
acct
id
amount
note
1
100
keep me
The query — predict the output before reading on
INSERT INTO acct (id, amount) VALUES (1, 40)
ON CONFLICT (id) DO UPDATE SET amount = acct.amount + excluded.amount;
SELECT id, amount, note FROM acct;
Why they ask this
The name reads backwards to most people, and getting it wrong turns an accumulate into an overwrite or vice versa.
Say this
`excluded` is the row that was *excluded from being inserted* — the new incoming values. So `acct.amount + excluded.amount` is 100 + 40 = 140, and the untouched `note` column keeps its original value.
The reasoning
The naming makes sense once you see it from the engine's side: the insert was attempted, it conflicted, and the row it was going to insert is now excluded. Qualifying with the table name (`acct.amount`) refers to the row already stored. Having both available is what makes conditional and accumulating upserts expressible.
This snippet deliberately shows an accumulate, and that is worth flagging as a hazard rather than a pattern to copy. `SET amount = acct.amount + excluded.amount` is *not* idempotent: replay the same batch and the balance is 180. For a pipeline that retries, `SET amount = excluded.amount` is the safe form, and accumulation belongs in the query that reads the table, not in the write.
The `note` column demonstrates the other half: an upsert updates only the columns you name, so everything else survives. That is the concrete difference from a DELETE-then-INSERT, which would have wiped it.
What it actually returns verified against SQLite
100 + 40 = 140, and `note` survives because the upsert never mentioned it.
id
amount
note
1
140
keep me
The answer most people give
"`excluded` is the row already in the table." It is the opposite — the incoming row that could not be inserted. Reversing them turns an accumulate into an overwrite, which is a silent data change rather than an error.
They’ll ask next
This batch is delivered twice. What is the balance, and how would you write it so that it is not?
Two inserts, a savepoint between them, then a rollback to that savepoint and a commit. Which rows survive?
Given these rows
t
v
0 rows
The query — predict the output before reading on
BEGIN;
INSERT INTO t VALUES ('before savepoint');
SAVEPOINT sp;
INSERT INTO t VALUES ('after savepoint');
ROLLBACK TO sp;
INSERT INTO t VALUES ('after rollback');
COMMIT;
SELECT v FROM t;
Why they ask this
People assume a rollback ends the transaction. Rolling back *to a savepoint* does not — the transaction stays open and can still commit everything before it.
Say this
The first and third rows survive; only the insert after the savepoint is undone. `ROLLBACK TO` rewinds to the savepoint and leaves the transaction open, so the later insert and the commit both still apply.
The reasoning
A savepoint is a marker inside a transaction. `ROLLBACK TO sp` discards everything done since that marker and leaves you inside the same transaction, still able to do more work and still able to commit. That is the difference from a plain `ROLLBACK`, which discards the whole transaction and ends it.
This is the mechanism behind partial error handling in a long-running load: wrap each risky step in a savepoint, and a failure rolls back only that step rather than an hour of work. It is also how many drivers implement nested transactions, since real nesting does not exist.
One thing that trips people: the savepoint remains defined after a rollback to it, so you can roll back to the same name more than once. Releasing it with `RELEASE sp` merges its work into the enclosing transaction rather than undoing anything — a name that sounds destructive and is not.
What it actually returns verified against SQLite
v
before savepoint
after rollback
The answer most people give
"Nothing survives, the rollback killed the transaction." `ROLLBACK TO savepoint` rewinds to a marker; the transaction is still open and the later COMMIT still commits everything outside the rolled-back span.
They’ll ask next
Where would you use a savepoint in a nightly load, and what would you have to be careful about?
NTILE(4) over ten rows. How many rows land in each bucket, and which bucket gets the extras?
Given these rows
t
v
1
2
3
4
5
6
7
8
9
10
The query — predict the output before reading on
SELECT v, NTILE(4) OVER (ORDER BY v) AS bucket
FROM t
ORDER BY v;
Why they ask this
Quartiles get reached for whenever someone wants "the slowest quarter", and almost nobody checks what happens when the row count does not divide evenly.
Say this
Three, three, two, two. Ten does not divide by four, so NTILE gives the remainder to the earliest buckets — never the last ones — and it splits by row count rather than by value.
The reasoning
`NTILE(n)` divides the ordered rows into n groups **as equal in count as possible**. With ten rows and four buckets, the base size is 2 with a remainder of 2, and those two extra rows go to buckets 1 and 2. The result is 3, 3, 2, 2 — never 2, 2, 3, 3.
**The consequence people miss is what happens to ties.** NTILE assigns by position, not by value, so two rows holding exactly the same value can land either side of a boundary. If those values are latencies and the buckets become an SLA band, two identical requests get graded differently. Nothing errors and nothing looks wrong in the output.
**So the honest rule:** NTILE answers "split these rows into n equal-sized groups". If the question is really "group by value threshold" — everything under 30 minutes is fast — then NTILE is the wrong tool and a CASE over explicit boundaries is the right one. The two only agree when there are no ties near a boundary, which is not a property you can rely on.
Worth knowing the neighbours too: `PERCENT_RANK` and `CUME_DIST` describe a row position as a fraction, and `PERCENTILE_DISC`/`PERCENTILE_CONT` pick a value at a fraction. NTILE is the only one that labels every row with a bucket, which is why it is the one used for deciles and quartiles.
What it actually returns verified against SQLite
v
bucket
1
1
2
1
3
1
4
2
5
2
6
2
7
3
8
3
9
4
10
4
The answer most people give
"Two in each, with the last two buckets taking the extras." Both halves are wrong: the buckets are uneven, and the remainder always goes to the earliest buckets. Guessing the direction is a coin flip you can just know.
They’ll ask next
Two deliveries took exactly the same time and landed in different quartiles. Is that a bug?
A list is paginated with ORDER BY created_at, LIMIT 20 OFFSET 20. Users report a row appearing on two pages. What happened?
Why they ask this
It is the most common pagination bug in production, it cannot be reproduced on demand, and the fix is one column.
Say this
created_at is not unique, so rows sharing a timestamp have no defined order. Each query is free to order the tied rows differently, so page 1 and page 2 get cut from two different lists — one row repeats and another is never shown.
The reasoning
**LIMIT and OFFSET are positions in an ordering, and the ordering here is partial.** If forty rows share a `created_at` value, the standard says nothing about their relative order. The engine may return them in index order on one run and in a different order on the next, because a parallel scan finished in a different sequence or the plan changed.
**That makes the page boundary move.** Page 1 takes rows 1–20 of one arrangement, page 2 skips 20 of a *different* arrangement. A row that sat at position 20 the first time and 21 the second appears on both pages; the row it displaced appears on neither. Nothing errors, and it is unreproducible precisely because it depends on timing.
**The fix is to make the sort total:** append a unique column, almost always the primary key — `ORDER BY created_at DESC, id DESC`. Now no two rows compare as equal, the ordering is fully determined, and the page seam is stable across runs.
**The second-order problem is cost.** OFFSET does not skip work: the engine produces every row it steps over and discards it, so page 900 does nine hundred pages of effort. Once a list is long enough for anyone to reach page 900, the fix is a keyset cursor — `WHERE (created_at, id) < (:last_at, :last_id)` — which starts at the row the client last saw and costs the same at every depth.
The answer most people give
"Add DISTINCT" or "the data changed between requests." DISTINCT deduplicates within one result set and does nothing across pages; concurrent inserts shift pages too, but they do not explain a duplicate on a static table, and the tie-break fixes the reported symptom either way.
They’ll ask next
Your sort key is a unique column already. Is OFFSET now safe at page 900?
