SQL0 Lab — cause a wrong number, then prove it

Target: any SQL engine you can create tables in — Postgres, DuckDB, SQLite, Redshift, Athena, or Spark SQL. Parts 1–5 are engine-neutral. Part 6 is Redshift-specific and optional.

Cost: zero on a local engine. On Redshift/Athena, trivial — the tables are a handful of rows.

Write your answers down. Predict before you run, every time. The gap between your prediction and the result is the entire value of this lab.

Setup

CREATE TABLE customers (customer_id INT, name VARCHAR(50), tier VARCHAR(10));
INSERT INTO customers VALUES
  (1,'Acme','gold'), (2,'Borg','silver'), (3,'Cyan',NULL),
  (2,'Borg','gold');                      -- deliberate duplicate

CREATE TABLE orders (order_id INT, customer_id INT, amount DECIMAL(10,2), status VARCHAR(10));
INSERT INTO orders VALUES
  (10,1,100.00,'paid'), (11,1,200.00,'paid'), (12,2,50.00,'refunded'),
  (13,3,75.00,'paid'),  (14,NULL,25.00,'paid');   -- order with no customer

CREATE TABLE blocked (customer_id INT);
INSERT INTO blocked VALUES (3), (NULL);           -- deliberate NULL

CREATE TABLE shipments (ship_id INT, order_id INT, weight DECIMAL(10,2));
INSERT INTO shipments VALUES (100,10,5.0),(101,10,3.0),(102,10,2.0),(103,11,1.0);

CREATE TABLE returns (ret_id INT, order_id INT, refund DECIMAL(10,2));
INSERT INTO returns VALUES (200,10,10.00),(201,10,20.00);

Order 10 has 3 shipments and 2 returns. Customer 2 appears twice. blocked contains a NULL. Order 14 has a NULL customer. Customer 3 has a NULL tier.

Part 1 — NULL

Predict each answer before running.

SELECT 7 = NULL, 7 <> NULL, NULL = NULL;
SELECT COUNT(*) FROM orders WHERE customer_id NOT IN (SELECT customer_id FROM blocked);
SELECT COUNT(*) FROM orders WHERE customer_id IN     (SELECT customer_id FROM blocked);
SELECT COUNT(*) FROM customers WHERE tier <> 'gold';

Q1. What did you predict for the NOT IN count, and what did you get? Explain the gap in one sentence.

Q2. Why does the IN version behave differently from the NOT IN version?

Q3. tier <> 'gold' — is customer 3 (NULL tier) in the result? Should they be? Rewrite it two ways so they are.

Q4. Rewrite the NOT IN query with NOT EXISTS and confirm the count. Which would you ship, and why?

Part 2 — Fan-out ⚠️

-- Ground truth
SELECT SUM(amount) FROM orders;

-- After joining the dimension
SELECT SUM(o.amount) FROM orders o JOIN customers c ON c.customer_id = o.customer_id;

Q5. Are these equal? By how much do they differ, and which customer caused it?

Q6. Run the row-count-before/after check. Write the exact query you used.

Q7. Run the uniqueness check on customers. What does it return?

Q8. Now fix it three different ways and confirm each gives the ground-truth total: (a) deduplicate customers with ROW_NUMBER, (b) use EXISTS instead of the join, (c) aggregate before joining. Which would you put in production, and why?

Q9. Order 14 has customer_id = NULL. Does it survive the inner join? Does it survive a LEFT JOIN? What does that mean for your revenue total?

Part 3 — The chasm trap ⚠️

SELECT o.order_id, SUM(s.weight) AS wt, SUM(r.refund) AS refund
FROM orders o
LEFT JOIN shipments s ON s.order_id = o.order_id
LEFT JOIN returns   r ON r.order_id = o.order_id
WHERE o.order_id = 10
GROUP BY o.order_id;

Q10. Predict wt and refund for order 10 before running. True shipment weight is 10.0 and true refund is 30.00. What did you get?

Q11. How many rows does the un-aggregated join produce for order 10? Show the arithmetic.

Q12. By what factor is each aggregate wrong, and why are the two factors different?

Q13. Rewrite it correctly. Confirm you get 10.0 and 30.00.

Part 4 — Counting

SELECT COUNT(*), COUNT(customer_id), COUNT(DISTINCT customer_id) FROM orders;
SELECT SUM(amount), COUNT(*) FROM orders WHERE status = 'cancelled';   -- matches nothing

Q14. Explain each of the three counts in one clause each.

Q15. The second query: what is SUM and what is COUNT? Why are they different types of answer?

Q16. Write one query returning, in a single pass: total orders, paid orders, refunded orders, and distinct customers who have a paid order.

Q17. Now the LEFT JOIN count trap:

SELECT o.order_id, COUNT(*) AS a, COUNT(s.ship_id) AS b
FROM orders o LEFT JOIN shipments s ON s.order_id = o.order_id
GROUP BY o.order_id ORDER BY o.order_id;

For order 12 (no shipments), what are a and b? Which is correct and why?

Part 5 — Dedup and determinism ⚠️

CREATE TABLE events (customer_id INT, updated_at TIMESTAMP, payload VARCHAR(20));
INSERT INTO events VALUES
  (1,'2026-01-01 10:00','a'), (1,'2026-01-02 10:00','b'),
  (2,'2026-01-01 10:00','c'), (2,'2026-01-01 10:00','d'),   -- exact tie
  (3, NULL,               'e'), (3,'2026-01-01 10:00','f'); -- NULL timestamp

Q18. Write the latest-row-per-customer dedup with ROW_NUMBER. What do you get for customer 2? Run it several times — is it always the same row? Can you promise it will be?

Q19. For customer 3, which row wins? Where did the NULL sort? Is that what you want?

Q20. Add a deterministic tie-breaker and a NULL handling rule. Explain both choices in one sentence each.

Q21. Replace ROW_NUMBER() with RANK(). What happens to customer 2, and what does that mean for "deduplicated"?

Q22. Run both dedup assertions: output unique by key, and no keys lost. Show the queries.

Q23. Try SELECT DISTINCT customer_id, updated_at, payload FROM events. Does it deduplicate by customer? Why not?

Part 6 — Redshift only ⚠️ (optional)

Skip unless you have a Redshift cluster or Serverless workgroup.

CREATE TABLE dim_test (id INT PRIMARY KEY, val VARCHAR(10));
INSERT INTO dim_test VALUES (1,'a'), (1,'b');   -- same "primary key" twice
SELECT * FROM dim_test;

Q24. Did the insert succeed? Did the select return both rows? What does that tell you about constraint enforcement?

Q25. In your own words, why is this more dangerous than simply "the constraint doesn't work"?

Q26. Try ORDER BY updated_at DESC NULLS LAST inside an OVER (...) clause. What happens? What's your workaround?

Q27. Try SELECT * FROM (VALUES (1,'a'),(2,'b')) t(id,name). What happens? Rewrite it a way that works.

Q28. Audit your real cluster: list tables with declared primary keys, then run the uniqueness check against two of them. Any surprises?

Teardown

DROP TABLE customers; DROP TABLE orders; DROP TABLE blocked;
DROP TABLE shipments; DROP TABLE returns; DROP TABLE events;
DROP TABLE dim_test;   -- Part 6 only

Done when you can

Predict any NULL expression's result without running it
Explain why NOT IN fails but IN doesn't, from the AND/OR expansion
Detect fan-out with a row count in under ten seconds
Recognise a chasm trap from the table shapes alone, before writing SQL
Say which of COUNT(*), COUNT(col), COUNT(DISTINCT col) answers a given question
Write a deterministic dedup, and say which row wins and why
Explain why a false primary key in Redshift can produce a wrong result

Facilitator notes

Predict-then-run is the whole method. Have people write predictions on paper for Parts 1 and 3 before touching a keyboard. The NOT IN and chasm-trap reveals are worth far more when there's a wrong prediction on the table in their own handwriting.
Part 3 is the centrepiece. Do the 3 × 2 = 6 arithmetic on a whiteboard before showing the query output. Everyone can multiply; not everyone can read a plan.
Part 5 Q18 needs patience. On a small local engine the tie may look stable across runs. Make the point that "stable on six rows on my laptop" is not "deterministic at scale on a distributed engine" — that's exactly why it reaches production.
Part 6 lands hardest if you do it live on a shared screen. The duplicate row sitting under a declared primary key silences a room.
Timing: 60 minutes hands-on, 20 debrief. Parts 1, 2 and 3 are non-negotiable; 5 can be a demo if time is short.