Chapter 12 — Further Reading

Official reference (everyone)

  • PostgreSQL Docs: "Window Functions" (tutorial) and "Window Function Calls" (syntax) and "Window Function Processing." The complete, authoritative treatment — OVER, PARTITION BY, frames (ROWS/RANGE/GROUPS), and the default-frame rule behind Case Study 2. https://www.postgresql.org/docs/current/tutorial-window.html
  • PostgreSQL Docs: "General-Purpose Window Functions." row_number, rank, dense_rank, ntile, lag, lead, first_value, last_value, nth_value — each with its exact semantics.

Learn the mental model (📊 Analyst · 💻 Developer)

  • "SQL Window Functions explained" tutorials (Mode, PostgreSQL wiki, dataschool). Visual walkthroughs of partitions and frames make the concepts click fast.
  • pgexercises.com — "Window functions" section. Excellent graduated drills (running totals, ranks, moving averages) on a real schema.
  • "Frames in SQL window functions" deep dives — the part everyone skips and then gets bitten by (Case Study 2). Worth a focused read.

Patterns & performance (💻 Developer · 🏗️ DBA)

  • "Top N per group in SQL" comparisons (window function vs. lateral join vs. correlated subquery). The window approach is usually cleanest; good to see the alternatives.
  • Articles on replacing correlated subqueries / self-joins with window functions. Reinforces Case Study 1's O(n²) → O(n) win.
  • EXPLAIN and the WindowAgg node. Preview of Chapter 24 — how to confirm the database is doing a single windowed pass.

Going further (🔬 CS Student)

  • GROUPS frame mode and EXCLUDE clauses. Advanced frame control beyond ROWS/RANGE.
  • LATERAL joins as an alternative for some per-row computations (ties to Chapter 9). Knowing both tools helps you pick.

Reference (this book)

  • Appendix C — SQL Quick Reference: window-function syntax and the common patterns.
  • Appendix I — SQL Cookbook: ready recipes — top-N-per-group, running total, moving average, period-over-period, % of total.

Do, don't just read

  • Build each pattern once on Mercado: top-2-per-category, running revenue, monthly LAG change, 3-day moving average. They become reflexes fast.
  • Reproduce the LAST_VALUE trap, then fix it both ways. You'll never be fooled by a default frame again.
  • Rewrite a correlated-subquery running total as a window function and compare with EXPLAIN ANALYZE (after Chapter 24, on generate_data.sql).

Next: Chapter 13 — Data Modification: INSERT, UPDATE, DELETE, and UPSERT — changing data safely.