Pair your devices with a code and playback position follows you: pause on this device, hit resume on the other. Position is saved to the site every minute and on pause.
Open this panel on your other device and enter the same code.
18 questions. Four options each, one correct answer, no partial credit.
1. SELECT 7 <> NULL returns:
truefalseNULL2. WHERE status <> 'shipped' — rows where status is NULL are:
3. customer_id NOT IN (SELECT customer_id FROM blocked) where blocked contains a NULL
returns:
NULL customer_id4. Why doesn't plain IN have the same failure?
IN ignores NULL by definitionIN expands to OR, and true OR unknown is trueIN implicitly adds IS NOT NULL5. NULL AND false evaluates to:
NULLtruefalse6. For SCD change detection, <> is wrong because:
IS DISTINCT FROMNULL→value transition evaluates to unknown and is missed7. SUM(amount) over a filter matching zero rows returns:
0NULL8. A many-to-one join where the "one" side has a duplicate key causes:
9. The fastest check for fan-out is:
SELECT DISTINCTNULLs10. Orders joined to shipments and returns, all keys valid, both SUMs wrong. This is:
NULL propagation bugGROUP BY grain11. Putting s.status = 'delivered' in WHERE on a LEFT JOIN:
12. After a LEFT JOIN, which returns 0 for unmatched parents?
COUNT(*)COUNT(1)COUNT(right_table.column)COUNT(DISTINCT *)13. COALESCE(COUNT(x), 0) is:
COUNT never returns NULLLEFT JOINsCOUNT(x)14. WHERE ROW_NUMBER() OVER (...) = 1 fails because:
ROW_NUMBERWHERE is evaluated before window functionsDISTINCTPARTITION BY is missing15. Deduplicating with RANK() instead of ROW_NUMBER():
NULLs16. Omitting a tie-breaker in a dedup ORDER BY causes:
NULLs to sort first17. In Redshift, a declared PRIMARY KEY is:
COPY18. Which is on Redshift's unsupported PostgreSQL feature list?
GROUP BYNULLS clause in window functionsLEFT JOIN1 — C. "Ordinary comparison operators yield null (signifying 'unknown'), not true or false, when
either input is null." <> is an ordinary comparison operator, so it yields NULL too — which
surprises people who expect the negation to be true.
2 — B. NULL <> 'shipped' is unknown, and WHERE keeps only rows where the condition is true.
Nullability of the column doesn't change the semantics (C), and it's not an error (D).
3 — C. NOT IN expands to an AND chain of <> comparisons; the NULL term is unknown, and
true AND unknown is unknown, so no row satisfies it. Empty result from an apparently correct query.
4 — B. IN is an OR chain, and true OR unknown is true, so matching rows still return. A is
wrong — IN doesn't ignore NULL, it's just that OR short-circuits favourably. This asymmetry is
why the bug survives testing.
5 — C. false. The unknown can't change the outcome — false AND anything is false. This is the
one case where three-valued logic collapses to a definite answer.
6 — B. NULL <> 'x' is unknown, not true, so the change isn't flagged and the update is silently
missed. IS DISTINCT FROM treats null as an ordinary value and catches it.
7 — B. Documented explicitly: "except for count, these functions return a null value when no
rows are selected. In particular, sum of no rows returns null, not zero as one might expect."
8 — C. A join is a filtered cross product. A duplicate on the "one" side multiplies matching rows
on the "many" side, and every downstream SUM inflates. No error is raised — that's the danger.
9 — B. Counting rows before and after takes seconds and directly detects multiplication. A is useful later; C hides the symptom and changes aggregates; D is a different bug class.
10 — B. Three shipments × two returns = six rows for one order. Shipment weight is counted twice, refunds three times — both wrong, by different factors. Fix by aggregating each branch separately first.
11 — C. Unmatched rows carry NULL on the right side, NULL = 'delivered' is unknown, and
WHERE drops them — exactly what an inner join does. Put the predicate in ON to preserve the left
side.
12 — C. COUNT(expr) counts "input rows in which the input value is not null". COUNT(*) and
COUNT(1) both count the row itself, which still exists after a left join, so they return 1.
13 — B. COUNT is the only aggregate that returns a value (0) over zero rows, so it can never be
NULL. Seeing this in code usually indicates the author was pattern-matching rather than reasoning.
14 — B. Logical evaluation order is FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Window
functions are evaluated with SELECT, after WHERE. Wrap in a subquery or CTE, or use QUALIFY
where supported.
15 — C. RANK() assigns tied rows the same rank, so a tie at the top yields two rows with rank 1
and the "deduplicated" output still has duplicates. It only manifests when real data contains a tie.
16 — B. The winner is undefined; the engine may choose differently per run depending on plan and parallelism. The pipeline then produces varying output from identical input — and the bug won't reproduce on demand.
17 — C. "Unique, primary key, and foreign key constraints are permitted, but they are informational only. They are not enforced by the system, but they are used by the query planner." The second clause means a false declaration can yield wrong results, not merely slow ones.
18 — C. The unsupported list includes "NULLS clause in Window functions" — window functions themselves are supported. This matters directly for dedup ordering on nullable columns.
| Score | Reading |
|---|---|
| 16–18 | Solid. Go to SQL1 (window functions) and rehearse the interview file out loud. |
| 12–15 | Good. Re-read lessons 1 and 2 — NULL and fan-out carry most of the risk. |
| 8–11 | Do the lab. These need to be reflexes, not recall. |
| ≤ 7 | Start at lesson 1. Everything here compounds. |
The five that matter most in production: 3 (NOT IN), 8 (fan-out), 10 (chasm trap), 16
(non-deterministic dedup), 17 (Redshift constraints). Each one produces a plausible wrong number
rather than an error.