R08 · Week 8 Recall & Drill

Weekly revision · Week 8 · Covers 5 sessions from Mon–Fri.

Sessions covered

• S036 — SQL Basics — SELECT, WHERE, ORDER BY, LIMIT
• S037 — Joins — INNER, LEFT, RIGHT, FULL, Anti-Join
• S038 — Aggregations — GROUP BY, HAVING, Subqueries
• S039 — Window Functions — the Game-Changer
• S040 — CTEs & Recursive Queries

90-min structure

Blank-page reconstruction · 30 min

S036 · SQL Basics

1. Write the logical execution order of a query, all eight stages, in order.
2. Explain why comparing a column to null with an equality operator returns no rows, and give the correct form.
3. Give two concrete production reasons to avoid selecting every column.

Gotcha you probably forgot: offset-based pagination degrades as the offset grows, because the engine must still produce and discard every skipped row. Keyset pagination — remembering the last key you saw and filtering greater than it — stays fast at any depth because the index seeks straight to the position. The user-visible symptom is that page 1 is instant and page 500 times out.

S037 · Joins

1. Build the decision table: inner, left, full outer, anti — one business question each.
2. Explain row multiplication in one sentence, with a concrete example of how it inflates a sum.
3. Write an anti-join both ways, and say when you would prefer each.

Gotcha you probably forgot: counting rows after a left join over-reports customers with no orders as one instead of zero, because the outer join manufactures a row full of nulls and row-counting counts it. Count the joined column instead of counting rows, since that skips nulls and gives the honest zero.

S038 · Aggregations

1. Explain the difference between filtering before grouping and filtering after grouping, and give a condition only the latter can express.
2. Say what an aggregate returns when no rows match, and how to make it return zero instead.
3. Describe the average-of-averages bug with a concrete example.

Gotcha you probably forgot: every aggregate except row-counting skips nulls. That means averaging a column divides by the count of non-null values, not by the number of rows — which is almost never the average the person asking actually wanted. If nulls mean zero in your domain, coalesce them explicitly before aggregating.

S039 · Window Functions

1. State the fundamental difference between grouping and windowing, in terms of rows in versus rows out.
2. Given a salary list containing a tie, write out what row-number, rank, and dense-rank each return.
3. Explain why you cannot filter on a window function's result in the same select, and what the standard workaround is.

Gotcha you probably forgot: the default frame is not what most people assume. When you supply an ordering without an explicit frame clause, the default frame includes all peer rows tied at the current ordering value — which for a running total over a column with duplicate values jumps by the whole tied group at once instead of one row at a time. Specify the frame explicitly whenever the answer depends on it.

S040 · CTEs & Recursive Queries

1. Name the four required parts of a recursive CTE.
2. Explain why the recursive branch uses the duplicate-preserving union rather than the deduplicating one.
3. Name three real hierarchies other than an org chart that fit a recursive CTE.

Gotcha you probably forgot: a CTE is not reliably a temporary table. Whether the engine materialises it once or inlines it into every reference is engine- and plan-dependent, and both choices have costs — inlining can re-execute expensive work, while materialising can block the optimiser from pushing your filters down into the CTE. Referencing a CTE twice is not automatically free.

Hands-on drill · 30 min

Task: build one small database and reproduce every trap from this week, so you have seen the wrong number rather than been warned about it.

Step 1 — the schema and data (5 min)

mkdir -p ~/projects/w8-drill && cd ~/projects/w8-drill

-- setup.sql
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
 
CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  tier TEXT                      -- deliberately nullable
);
 
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  placed_on   TEXT NOT NULL,
  amount      REAL                -- deliberately nullable
);
 
CREATE TABLE order_items (
  id       INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  sku      TEXT NOT NULL
);
 
INSERT INTO customers VALUES
  (1,'Ada','gold'), (2,'Bob','gold'), (3,'Cleo',NULL), (4,'Dan','silver');
 
INSERT INTO orders VALUES
  (10,1,'2024-01-05',100.0),
  (11,1,'2024-01-20',200.0),
  (12,2,'2024-02-02',50.0),
  (13,2,'2024-02-11',NULL),      -- amount unknown
  (14,4,'2024-03-01',75.0);
  -- Cleo has no orders at all.
 
INSERT INTO order_items VALUES
  (100,10,'sku-a'), (101,10,'sku-b'), (102,10,'sku-c'),
  (103,11,'sku-a'),
  (104,12,'sku-d'), (105,12,'sku-e');

sqlite3 drill.db < setup.sql && echo "loaded"

Step 2 — reproduce the traps (12 min)

Predict every answer on paper first, then run it.

-- traps.sql
.headers on
.mode column
 
SELECT '--- 1. null equality ---';
SELECT count(*) AS matched_with_equals FROM customers WHERE tier = NULL;
SELECT count(*) AS matched_with_is_null FROM customers WHERE tier IS NULL;
 
SELECT '--- 2. count(*) vs count(col) after LEFT JOIN ---';
SELECT c.name,
       count(*)    AS wrong_order_count,
       count(o.id) AS right_order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
 
SELECT '--- 3. row multiplication inflates SUM ---';
SELECT sum(amount) AS honest_total FROM orders;
SELECT sum(o.amount) AS inflated_total
FROM orders o
JOIN order_items i ON i.order_id = o.id;
 
SELECT '--- 4. aggregates skip nulls ---';
SELECT count(*)      AS rows_total,
       count(amount) AS rows_with_amount,
       avg(amount)   AS avg_skipping_nulls,
       avg(coalesce(amount, 0)) AS avg_treating_null_as_zero
FROM orders WHERE customer_id = 2;
 
SELECT '--- 5. empty aggregate ---';
SELECT sum(amount) AS empty_sum,
       coalesce(sum(amount), 0) AS empty_sum_fixed
FROM orders WHERE customer_id = 3;

sqlite3 drill.db < traps.sql

Expected outcome, and check each against your prediction: the equality comparison matches zero rows while the null test matches one. Cleo shows a wrong count of 1 and a right count of 0. The inflated total is strictly larger than the honest total, because the order with three items is counted three times — this is the bug that silently corrupts revenue dashboards. Bob's row count exceeds his count of amounts, so the two averages differ. And the empty sum is null, not zero, until you coalesce it.

Step 3 — windows and top-N-per-group (8 min)

-- windows.sql
.headers on
.mode column
 
-- Ranking family on a tie. Note gold appears twice.
SELECT name, tier,
       row_number() OVER (ORDER BY tier)  AS rn,
       rank()       OVER (ORDER BY tier)  AS rnk,
       dense_rank() OVER (ORDER BY tier)  AS drnk
FROM customers;
 
-- Top order per customer: window in a CTE, filter outside it.
WITH ranked AS (
  SELECT o.id, o.customer_id, o.amount,
         row_number() OVER (
           PARTITION BY o.customer_id
           ORDER BY o.amount DESC
         ) AS rn
  FROM orders o
)
SELECT c.name, r.id AS order_id, r.amount
FROM ranked r
JOIN customers c ON c.id = r.customer_id
WHERE r.rn = 1
ORDER BY c.name;
 
-- Running total: frame stated explicitly, not left to the default.
SELECT id, placed_on, amount,
       sum(coalesce(amount, 0)) OVER (
         ORDER BY placed_on
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY placed_on;

Expected outcome: on the tie, row-number assigns distinct consecutive integers, rank leaves a gap after the tied pair, and dense-rank does not. The top-order query returns exactly one row per customer that has orders — and note Cleo is absent, which is correct for an inner join and would be a bug if the question were "every customer". The running total increases monotonically and never resets, because the frame was stated explicitly.

Step 4 — recursive CTE with a guard (5 min)

-- recursive.sql
WITH RECURSIVE mondays(d, n) AS (
    SELECT date('2026-01-05'), 1               -- base case: first Monday of 2026
  UNION ALL
    SELECT date(d, '+7 days'), n + 1           -- recursive step
    FROM mondays
    WHERE d < '2026-12-31' AND n < 100         -- termination + depth guard
)
SELECT count(*) AS monday_count, min(d) AS first, max(d) AS last FROM mondays;

Expected outcome: a count in the low fifties, first date 2026-01-05, last date in late December. The n < 100 clause is the drill's real point — it is redundant here because the date condition already terminates, but on hierarchical data with an accidental cycle the date-style condition never fires and the query runs until it exhausts memory. Write the depth guard every time; it costs nothing and it is the difference between a wrong answer and a dead database.

Gap analysis + next week preview · 10 min

• How many of the five traps in Step 2 did you predict correctly before running them? Each miss is a class of silently wrong number you would have shipped.
• Did the inflated total surprise you? If so, adopt the before-and-after row count check as a permanent habit.
• Can you explain, without notes, why filtering on a window result needs a wrapper query? That single fact separates people who use window functions from people who fight them.

Next week (S041–S045) moves under the query language into how the database actually works: index structures and when to add one; transactions, ACID, isolation levels and multi-version concurrency; query planning with execution plans and tuning; the NoSQL landscape across key-value, document, column and graph stores; and dimensional data modelling. Everything you just wrote will get faster or slower depending on the index choices in S041.

Part of the 6-month evergreen learning plan.