Exercises: SQL Transformations

Almost all of these are code. Run them against your Kestrel database — the answers are more useful when you have seen the row counts.

Difficulty: ⭐ warm-up · ⭐⭐ standard · ⭐⭐⭐ deeper. Solutions: daggered (†) and odd-numbered problems are in appendices/answers-to-selected.md.

Part A — Warm-ups ⭐

18.1 † Name the three tells that you are thinking procedurally rather than set-based, and what each is usually replaceable by.

18.2 What is the difference between a window function and GROUP BY, in one sentence?

18.3 † Why must deduplication use ROW_NUMBER rather than RANK?

18.4 What is the default frame when a window has an ORDER BY? What is it without one? What does adding an ORDER BY silently change about SUM?

18.5 † Explain why LAST_VALUE returns the current row under the default frame, and give two fixes. Then state what it returns when the ORDER BY has ties — it is not the current row, and it is not the partition's last either.

18.6 Are CTEs optimization barriers? Answer precisely, naming the engine and version where the claim was true.

18.7 † Why UNION ALL rather than UNION in a recursive CTE? What does the depth guard convert an infinite loop into?

18.8 Name the four deduplication patterns. Which one silently composes rows that never existed, and how?

18.9 † State the general gaps-and-islands technique in one sentence.

18.10 What artifact does a backward-looking window function produce at a batch boundary, and why does it survive?

Part B — Standard ⭐⭐

18.11 Rewrite each as a single set-based statement: (a) a Python loop fetching each customer's order count one at a time; (b) a correlated subquery computing each order's rank within its customer; (c) a self-join comparing each order to the customer's previous order. Run all three against Kestrel and compare execution times.

18.12 † Demonstrate the RANGE versus ROWS bug. Construct a query over Kestrel's fct_order where two orders share a date, show the running total under the default frame, then fix it with ROWS. Report both outputs.

18.13 Write the category hierarchy query from §18.5. Then introduce a cycle into silver.categories and run it again. Record what happens with the depth guard and — carefully, with a statement timeout — what happens without it.

18.14 † Write all four deduplication patterns from §18.7 against bronze.orders_cdc. For each, report the row count and whether it is correct. Pattern 3 should produce a wrong answer — show which columns come from different source rows.

18.15 Implement the streak query from §18.8 against Kestrel: customers with three or more consecutive days of orders. Then rewrite it using the general "flag the breaks, running sum" form rather than date - rn, and confirm they agree.

18.16 † Implement the sessionization from §18.9. Then reproduce the boundary artifact: run it on two adjacent date ranges without overlap and count sessions under 30 seconds starting within five minutes of the window start. Add the overlap and re-count. Report both numbers.

18.17 Take silver.sessions and read its query plan. Count the sort operations. Then consolidate the window functions onto a common PARTITION BY ... ORDER BY where possible and count again. Report the sorts before and after, and the execution time.

18.18 † Write the pivot from §18.6 for revenue by category and channel. Then add a fifth channel to dim_channel and re-run. What happened to the output's columns, and what would break downstream? Propose the fix.

Part C — Deeper ⭐⭐⭐

18.19 §18.1's 📐 callout claims most "inherently sequential" problems are window functions or recursive CTEs. Find a genuine counterexample — a calculation that is truly path-dependent in a way SQL cannot express — and explain what makes it different.

18.20 † Design the test that catches a non-deterministic deduplication. It must fail when the tiebreaker is missing and pass when it is present, and it must not depend on running the query twice and hoping the order differs. State what you assume about the engine.

18.21 §18.10 says consolidating window functions onto a common partitioning is "frequently a large, free win." Quantify it: construct a query with four window functions using three different partitionings, measure it, then consolidate to two and measure again. Report the sorts and the time.

18.22 † The sessionization in §18.9 takes MAX(customer_id) because identity is resolved at login. Is that correct in every case? Construct the scenario where it is wrong, and propose a better rule.

Part D — The Kestrel Platform ⭐⭐⭐

18.23 — Increment 18: the silver models.

(a) Write silver.orders, silver.order_items, and silver.events as SQL files in platform/transform/silver/, using §18.7's deduplication with a unique tiebreaker.

(b) Write silver.sessions with §18.9's sessionization including the midnight rule.

(c) Prove the deduplication, in both directions. Rows equal distinct keys, and no keys dropped. Two assertions, because a dedup fails two ways.

(d) Reproduce and fix the boundary artifact, recording both counts.

(e) Read the plan for silver.sessions, count the sorts, consolidate, count again.

18.24 † Add platform/transform/silver/tests/ with an assertion per model: grain uniqueness, no dropped keys, no nulls in declared-not-null columns, and — for sessions — that no session spans midnight UTC and none exceeds a plausible maximum duration.

The last one is worth writing carefully. A session of 47 hours means the sessionization logic has a bug or the clock did; either way you want to know, and "plausible maximum" is a judgment you should write down.

Reflection

A. §18.3's frame-clause defaults are documented, unsurprising to the specification's authors, and responsible for roughly half of window-function bugs. What does that suggest about the difference between "documented" and "discoverable"?

B. This chapter's material is the oldest in the book and the most transferable. Of everything you have read so far, what else do you expect to still be true in twenty years?