OVER, PARTITION BY, frame semantics, and the analytical patterns — rankings, running totals, gaps & islands — that replace application-side loops.
Window functions are the most underused tool in SQL. Unlike aggregates, they return one row per input row — so you can rank, compare to neighbours, and compute running totals without losing the detail rows you started with. These lessons cover the fundamentals through advanced analytical patterns.
Paired course
Master Window FunctionsGo deeper with structured exercises, datasets, and production patterns.
OVER turns any aggregate into a window function — computing a result across a set of rows while still returning every row intact.
Three ranking functions, three different answers to ties. Pick the one that matches what your data actually means.
SUM OVER with ORDER BY gives you a running total. Add a ROWS BETWEEN frame and you get a sliding window — a moving average that looks back exactly N rows.
A window frame defines exactly which rows each window function sees. ROWS counts positions, RANGE counts values, GROUPS counts distinct peers. Mixing them up produces silent bugs.
Sessionization turns a stream of timestamped events into labelled sessions using nothing but a window function — no procedural loop required.
Go deeper
These lessons are the foundation. The book gives you the complete treatment, with real datasets you can run and the production patterns that turn thousands of lines of code into simple queries.
Get the Book — $89