Further Reading: SQL Transformations

Sources are tagged Tier 1 (confident it exists, recommended without reservation) or Tier 2 (real and worth seeking, but confirm the current edition, version, or URL yourself).

This is the best-served topic in the book. SQL is fifty years old, the window-function specification is thirty, and the literature has had time to become genuinely excellent. It is also the material with the longest shelf life here: nearly everything below will still be correct when the rest of this book needs a third edition.

The books

Itzik Ben-Gan, T-SQL Window Functions: For Data Analysis and Beyond (2nd ed., Microsoft Press, 2020). The definitive treatment of window functions by a wide margin. It is written against T-SQL, and roughly 85% of it is engine-independent — the frame-clause semantics, the ordering rules, the optimization discussion, and every technique in §18.8. Chapter 5 is where gaps and islands received its canonical treatment, and this book is the reason the phrase has a settled meaning. If you buy one book from this chapter, buy this one, and read past the dialect. Tier 1.
Anthony Molinaro and Robert de Graaf, SQL Cookbook (2nd ed., O'Reilly, 2020). Problem-first, with each recipe given in several dialects side by side — which is the fastest way to learn what is standard and what is one vendor's convenience. The pivot chapter (§18.6) and the "working with ranges of dates" material (§18.8) are directly relevant. The multi-dialect presentation is the point; reading the same problem solved four ways teaches portability better than any list of rules. Tier 1.
Joe Celko, SQL for Smarties: Advanced SQL Programming (5th ed., Morgan Kaufmann, 2014). The book that made "think in sets" a phrase data people say. Celko is opinionated to the point of being combative, some of the material predates window functions and shows it, and the tone is not to everyone's taste — but §18.1's argument is his argument, made at book length and better than this chapter makes it. Read it for the reorientation. Tier 1.
Markus Winand, SQL Performance Explained (2012), and the companion site use-the-index-luke.com. The clearest available explanation of how indexes interact with ORDER BY, GROUP BY, and window functions — which is exactly the material behind §18.10's "sorts you did not ask for." The website is free, complete, and maintained, and is the version to start with. Winand's modern-sql.com is the other half: what each SQL standard added, and which engines implement it, which is the reference for "can I use QUALIFY?" Tier 1 for the sites; Tier 2 for the print edition's currency.

The specification, and the parts of it worth reading

Your engine's documentation on window functions. For PostgreSQL this is the "Window Functions" section of the tutorial plus §4.2.8 of the SQL syntax chapter, and it is unusually good: the frame clause is documented precisely, including the RANGE-includes-peers behaviour that produces §18.3's running-total surprise. Read the frame-clause paragraph twice. Nearly every window bug in this chapter is that paragraph, correctly implemented, meeting an expectation formed elsewhere. Tier 1.
The WITH / WITH RECURSIVE documentation for your engine. PostgreSQL's is the reference implementation and its discussion of the MATERIALIZED and NOT MATERIALIZED keywords (added in 12) is the primary source for §18.4's correction of the optimization-barrier folklore. The recursive section documents the evaluation algorithm explicitly, which is what makes the UNION versus UNION ALL distinction obvious rather than memorized. Tier 1.
Modern SQL's feature tables, at modern-sql.com. Which engines support QUALIFY, FILTER, GROUPS framing, WINDOW clauses, and named window reuse — with the standard version each arrived in. This is the page to check before writing anything clever, and it will save you from discovering portability problems in review. Tier 2 — a maintained community resource; verify the specific claim you are relying on.

On plans and measurement

EXPLAIN (ANALYZE, BUFFERS) and the PostgreSQL documentation on "Using EXPLAIN." §18.10's material, from the source. The part most readers skip and most need: actual time is per execution of the node, and loops tells you how many executions there were. Case Study 1's 600× win is entirely contained in noticing loops=1904221. Tier 1.
explain.dalibo.com and pev2. Plan visualizers that compute node-level totals — including the actual time × loops product that the raw text makes you do in your head — and highlight the largest contributors. Paste a plan in, and the node responsible is usually visible in a second. Tier 2 — free hosted tools; check the current URL and consider whether pasting a plan externally is acceptable for your data.
Brendan Gregg, Systems Performance (2nd ed., Addison-Wesley, 2020), on the USE method. Not a SQL book, and included because Case Study 2's real lesson is a measurement lesson rather than a SQL one. Gregg's discipline — know what you are measuring, measure it a second way, be suspicious of numbers that confirm what you expected — is the thing that would have caught a fourteen-month artifact. Tier 1.

On the failure this chapter's second case study is about

Any good treatment of confirmation bias in operational data. The specific failure — a small, regular anomaly absorbed by an explanation that already existed — is not a SQL problem and does not have a canonical SQL-literature source. The closest useful reading is the incident-analysis literature: Sidney Dekker's The Field Guide to Understanding 'Human Error' (3rd ed., 2014) on why a plausible explanation stops an investigation, and why "everyone acted reasonably" and "the outcome was wrong" are routinely both true. It is written about aviation and healthcare and transfers without effort. Tier 1.
John Allspaw's writing on incident analysis, particularly on how organizations converge on a cause and stop. Relevant to §"What Happened" in both case studies: the hour <> 0 filter was added by a competent person acting on the best available explanation, and no amount of individual care would have prevented it. The control was a second implementation, not more diligence. Tier 2 — blog posts and talks; the material is scattered.

Practice

pgexercises.com. Free, PostgreSQL-based, and the aggregate and date sections cover this chapter's material with immediate feedback. The best available way to get window functions into your fingers rather than your notes. Tier 2 — free site; verify it is still up.
Advent of Code, solved in SQL. An idiosyncratic but effective exercise: many of the puzzles are naturally procedural, and forcing them into set-based SQL with recursive CTEs is the fastest way to find the edge of what §18.1's 📐 callout claims. Exercise 18.19 asks you to find a genuine counterexample; this is where to look for one. Tier 2 — an annual event; the archive persists.

A note on what to skip

The internet is full of SQL performance advice of the form "never use X." Most of it is a true observation about one engine and one version, promoted to a universal rule and then repeated for a decade after it stopped being true — "CTEs are optimization barriers" being the canonical example, correct for PostgreSQL through version 11 and wrong everywhere since.

The defense is the one this chapter uses throughout: name the engine and the version, or measure it. A performance claim without a version attached has an expiry date it does not disclose.