# Window functions > Use OVER, PARTITION BY and frames to compute rankings, running totals, moving averages and row-to-row comparisons without collapsing rows. SQL & Databases roadmap · Advanced SQL · 18 min read · https://levelcode.org/roadmaps/sql/window-functions `GROUP BY` answers "what is the total per genre?" but throws away the individual rows to do it. Often you want both: every book *and* its genre's average price, every order *and* the running total so far, every customer's order *and* how many days passed since the previous one. Window functions compute values across a set of related rows while keeping each row in the result. They are one of the most powerful features in SQL, and once they click you will use them constantly for reporting and analytics. ## The OVER clause Any aggregate becomes a window function when you add `OVER (...)`. The parentheses describe the **window**: which rows each row can "see" when the function is computed. ```sql SELECT title, genre, price, round(avg(price) OVER (PARTITION BY genre), 2) AS genre_avg FROM books ORDER BY genre, price DESC; ``` ```text title | genre | price | genre_avg ---------------------------+----------+-------+----------- A Wizard of Earthsea | fantasy | 12.99 | 11.62 Going Postal | fantasy | 11.50 | 11.62 Guards! Guards! | fantasy | 10.99 | 11.62 Small Gods | fantasy | 10.99 | 11.62 Kafka on the Shore | literary | 18.50 | 17.75 Norwegian Wood | literary | 17.00 | 17.75 The Dispossessed | sci-fi | 16.00 | 14.69 The Left Hand of Darkness | sci-fi | 15.50 | 14.69 Kindred | sci-fi | 14.00 | 14.69 Parable of the Sower | sci-fi | 13.25 | 14.69 (10 rows) ``` All ten rows survive. `PARTITION BY genre` splits the rows into groups, like `GROUP BY` does, but instead of collapsing each group the function's result is attached to every row in it. With an empty `OVER ()`, the whole result is one partition — handy for percentages: ```sql SELECT title, price, round(100 * price / sum(price) OVER (), 1) AS pct_of_catalogue FROM books ORDER BY price DESC LIMIT 3; ``` ```text title | price | pct_of_catalogue --------------------+-------+------------------ Kafka on the Shore | 18.50 | 13.1 Norwegian Wood | 17.00 | 12.1 The Dispossessed | 16.00 | 11.4 (3 rows) ``` A window definition has up to three parts, all optional: ```text OVER ( PARTITION BY ... -- which rows belong together ORDER BY ... -- the order of rows within each partition ROWS/RANGE ... -- the frame: which rows around the current one to include ) ``` ### Where window functions run Window functions are evaluated after `WHERE`, `GROUP BY` and `HAVING`, but before `DISTINCT`, `ORDER BY` and `LIMIT`. Two consequences follow. First, you can apply a window over aggregated results — `sum(sum(stock)) OVER ()` is the grand total across groups. Second, you cannot filter on a window function in `WHERE`: ```sql SELECT title, rank() OVER (ORDER BY price DESC) AS r FROM books WHERE r <= 3; ``` ```text ERROR: column "r" does not exist ``` Compute the window in a CTE or subquery, then filter the outer query. You will do this for every top-N problem. ## Ranking functions Ranking functions require an `ORDER BY` inside `OVER`. They differ only in how they treat ties. *Guards! Guards!* and *Small Gods* both cost 10.99: ```sql SELECT title, price, row_number() OVER w AS row_number, rank() OVER w AS rank, dense_rank() OVER w AS dense_rank FROM books WHERE genre = 'fantasy' WINDOW w AS (ORDER BY price) ORDER BY price, title; ``` ```text title | price | row_number | rank | dense_rank ----------------------+-------+------------+------+------------ Guards! Guards! | 10.99 | 1 | 1 | 1 Small Gods | 10.99 | 2 | 1 | 1 Going Postal | 11.50 | 3 | 3 | 2 A Wizard of Earthsea | 12.99 | 4 | 4 | 3 (4 rows) ``` | Function | Ties get | After a tie | Use it for | | --- | --- | --- | --- | | `row_number()` | different numbers (arbitrary order) | continues | exactly N rows per group, deduplication | | `rank()` | the same number | skips (1, 1, 3) | "top 3" where ties all qualify | | `dense_rank()` | the same number | no gap (1, 1, 2) | "the 3 highest distinct prices" | The `WINDOW` clause names a window definition so several functions can share it. Note that `row_number()` broke the tie arbitrarily; add a tie-breaker (`ORDER BY price, book_id`) whenever you need a stable result. ### Top N per group The two most expensive books in each genre — rank within each partition, then filter: ```sql WITH ranked AS ( SELECT title, genre, price, rank() OVER (PARTITION BY genre ORDER BY price DESC) AS r FROM books ) SELECT genre, title, price, r FROM ranked WHERE r <= 2 ORDER BY genre, r; ``` ```text genre | title | price | r ----------+---------------------------+-------+--- fantasy | A Wizard of Earthsea | 12.99 | 1 fantasy | Going Postal | 11.50 | 2 literary | Kafka on the Shore | 18.50 | 1 literary | Norwegian Wood | 17.00 | 2 sci-fi | The Dispossessed | 16.00 | 1 sci-fi | The Left Hand of Darkness | 15.50 | 2 (6 rows) ``` ## Running totals and frames Add `ORDER BY` to an aggregate's window and it becomes cumulative. Here is revenue over time, with order totals computed in a CTE first: ```sql WITH order_totals AS ( SELECT o.order_id, o.ordered_on, sum(oi.quantity * oi.unit_price) AS total FROM orders AS o JOIN order_items AS oi USING (order_id) WHERE o.status <> 'cancelled' GROUP BY o.order_id ) SELECT order_id, ordered_on, total, sum(total) OVER (ORDER BY ordered_on) AS running_total, round(avg(total) OVER (ORDER BY ordered_on ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3 FROM order_totals ORDER BY ordered_on; ``` ```text order_id | ordered_on | total | running_total | moving_avg_3 ----------+------------+-------+---------------+-------------- 1 | 2025-01-05 | 32.97 | 32.97 | 32.97 2 | 2025-01-12 | 17.00 | 49.97 | 24.99 3 | 2025-02-02 | 26.49 | 76.46 | 25.49 5 | 2025-02-20 | 41.25 | 117.71 | 28.25 6 | 2025-03-01 | 33.48 | 151.19 | 33.74 7 | 2025-03-08 | 38.97 | 190.16 | 37.90 8 | 2025-03-15 | 15.50 | 205.66 | 29.32 9 | 2025-03-22 | 31.00 | 236.66 | 28.49 10 | 2025-03-30 | 23.00 | 259.66 | 23.17 (9 rows) ``` The **frame** decides which rows of the partition feed the function for the current row. `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` means "this row and the two before it", a three-order moving average. The first two rows have fewer predecessors, so they average one and two values respectively. Frame boundaries can be `UNBOUNDED PRECEDING`, `n PRECEDING`, `CURRENT ROW`, `n FOLLOWING` and `UNBOUNDED FOLLOWING`. `ROWS` counts physical rows; `RANGE` works on values (`RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW` is a true seven-day window, however many orders fall in it). ### The default frame and peers When you write `ORDER BY` without a frame, the default is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. With `RANGE`, "current row" includes all its **peers** — rows with the same `ORDER BY` value. Watch what happens to the tied fantasy prices: ```sql SELECT title, price, stock, sum(stock) OVER (ORDER BY price) AS range_default, sum(stock) OVER (ORDER BY price, title ROWS UNBOUNDED PRECEDING) AS rows_frame FROM books WHERE genre = 'fantasy' ORDER BY price, title; ``` ```text title | price | stock | range_default | rows_frame ----------------------+-------+-------+---------------+------------ Guards! Guards! | 10.99 | 22 | 31 | 22 Small Gods | 10.99 | 9 | 31 | 31 Going Postal | 11.50 | 3 | 34 | 34 A Wizard of Earthsea | 12.99 | 14 | 48 | 48 (4 rows) ``` With the default frame, both 10.99 books show 31 — the sum through *all* peers — so the "running" total jumps instead of increasing row by row. The `ROWS` version adds one row at a time (with `title` as a tie-breaker so the order is stable). > [!WARNING] > For running totals, write the frame explicitly: `ORDER BY ordered_on, order_id ROWS UNBOUNDED PRECEDING`. If two orders ever share a date, the default `RANGE` frame silently gives them the same running total. ## Comparing rows: LAG, LEAD and friends `lag(expr)` returns the value of `expr` from the previous row in the partition; `lead(expr)` from the next. They make period-over-period comparisons easy. How long do customers wait between orders? ```sql SELECT c.name, o.order_id, o.ordered_on, lag(o.ordered_on) OVER w AS previous_order, o.ordered_on - lag(o.ordered_on) OVER w AS days_since FROM orders AS o JOIN customers AS c USING (customer_id) WHERE o.customer_id IN (1, 2) WINDOW w AS (PARTITION BY o.customer_id ORDER BY o.ordered_on) ORDER BY c.name, o.ordered_on; ``` ```text name | order_id | ordered_on | previous_order | days_since ------------+----------+------------+----------------+------------ Ada Park | 1 | 2025-01-05 | (null) | (null) Ada Park | 3 | 2025-02-02 | 2025-01-05 | 28 Ada Park | 8 | 2025-03-15 | 2025-02-02 | 41 Ben Okafor | 2 | 2025-01-12 | (null) | (null) Ben Okafor | 6 | 2025-03-01 | 2025-01-12 | 48 Ben Okafor | 10 | 2025-03-30 | 2025-03-01 | 29 (6 rows) ``` The first order in each partition has no predecessor, so `lag` returns `NULL`. You can supply an offset and a default: `lag(total, 1, 0)`. Subtracting two dates gives a number of days. `first_value(expr)` and `last_value(expr)` return values from the first and last rows of the frame. `last_value` has a notorious trap: with the default frame, the frame ends at the current row (and its peers), so `last_value` just returns the current row's value. To get the true last row of the partition, extend the frame: ```sql last_value(title) OVER ( ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) ``` Other useful functions: `ntile(4)` splits rows into four roughly equal buckets (quartiles), and `percent_rank()` and `cume_dist()` give relative positions between 0 and 1. > [!TIP] > `ORDER BY` inside `OVER` only controls the window computation. It does not sort the output — always add a final `ORDER BY` to the query if you care about row order. ## Combining windows in one query A single query can use several windows, and arithmetic can mix window results with ordinary columns. What share of each genre's copies does each book represent? ```sql SELECT title, genre, stock, sum(stock) OVER (PARTITION BY genre) AS genre_copies, round(100.0 * stock / sum(stock) OVER (PARTITION BY genre), 1) AS pct_of_genre FROM books ORDER BY genre, pct_of_genre DESC; ``` ```text title | genre | stock | genre_copies | pct_of_genre ---------------------------+----------+-------+--------------+-------------- Guards! Guards! | fantasy | 22 | 48 | 45.8 A Wizard of Earthsea | fantasy | 14 | 48 | 29.2 Small Gods | fantasy | 9 | 48 | 18.8 Going Postal | fantasy | 3 | 48 | 6.3 Norwegian Wood | literary | 8 | 8 | 100.0 Kafka on the Shore | literary | 0 | 8 | 0.0 Kindred | sci-fi | 11 | 22 | 50.0 The Left Hand of Darkness | sci-fi | 6 | 22 | 27.3 Parable of the Sower | sci-fi | 5 | 22 | 22.7 The Dispossessed | sci-fi | 0 | 22 | 0.0 (10 rows) ``` Note `100.0` rather than `100`: `stock` is an integer, so without a numeric literal the division would truncate to whole numbers. A few rules and costs to keep in mind as queries grow: - **Each distinct window needs its rows sorted** by its `PARTITION BY` and `ORDER BY` columns. Windows with identical definitions share one sort, so define them once with `WINDOW w AS (...)` rather than writing slight variations. An index whose columns match the partition and order keys can let PostgreSQL skip the sort entirely. - **Window functions cannot be nested** (`rank() OVER (ORDER BY sum(x) OVER ())` is an error) and cannot appear in `WHERE`, `GROUP BY` or `HAVING`. Compute them in a CTE and build on that. - **They can wrap aggregates**, because grouping happens first: `rank() OVER (ORDER BY sum(oi.quantity) DESC)` ranks the groups of a `GROUP BY` query in one step. ## Practice 1. For each customer's non-cancelled orders, show the order total, the order's sequence number for that customer (1 = first order), and the customer's cumulative spend up to and including that order. Sort by customer name and sequence number. 2. Using copies sold in non-cancelled orders, list the top two books per genre. If books tie, include all of them. **Solution** ```sql WITH order_totals AS ( SELECT o.order_id, o.customer_id, o.ordered_on, sum(oi.quantity * oi.unit_price) AS total FROM orders AS o JOIN order_items AS oi USING (order_id) WHERE o.status <> 'cancelled' GROUP BY o.order_id ) SELECT c.name, t.order_id, t.ordered_on, t.total, row_number() OVER w AS nth_order, sum(t.total) OVER w AS lifetime_spend FROM order_totals AS t JOIN customers AS c USING (customer_id) WINDOW w AS (PARTITION BY t.customer_id ORDER BY t.ordered_on, t.order_id ROWS UNBOUNDED PRECEDING) ORDER BY c.name, nth_order; ``` ```text name | order_id | ordered_on | total | nth_order | lifetime_spend -------------+----------+------------+-------+-----------+---------------- Ada Park | 1 | 2025-01-05 | 32.97 | 1 | 32.97 Ada Park | 3 | 2025-02-02 | 26.49 | 2 | 59.46 Ada Park | 8 | 2025-03-15 | 15.50 | 3 | 74.96 Ben Okafor | 2 | 2025-01-12 | 17.00 | 1 | 17.00 Ben Okafor | 6 | 2025-03-01 | 33.48 | 2 | 50.48 Ben Okafor | 10 | 2025-03-30 | 23.00 | 3 | 73.48 Dev Raman | 5 | 2025-02-20 | 41.25 | 1 | 41.25 Dev Raman | 9 | 2025-03-22 | 31.00 | 2 | 72.25 Elena Rossi | 7 | 2025-03-08 | 38.97 | 1 | 38.97 (9 rows) ``` The aggregation happens in the CTE; the window functions then run over one row per order. The explicit `ROWS` frame and the `order_id` tie-breaker keep the running total correct even if a customer placed two orders on the same day. ```sql WITH sales AS ( SELECT b.genre, b.title, sum(oi.quantity) AS copies FROM books AS b JOIN order_items AS oi USING (book_id) JOIN orders AS o USING (order_id) WHERE o.status <> 'cancelled' GROUP BY b.book_id ), ranked AS ( SELECT genre, title, copies, rank() OVER (PARTITION BY genre ORDER BY copies DESC) AS r FROM sales ) SELECT genre, title, copies, r FROM ranked WHERE r <= 2 ORDER BY genre, r, title; ``` ```text genre | title | copies | r ----------+---------------------------+--------+--- fantasy | A Wizard of Earthsea | 4 | 1 fantasy | Going Postal | 3 | 2 fantasy | Guards! Guards! | 3 | 2 literary | Norwegian Wood | 2 | 1 sci-fi | Kindred | 3 | 1 sci-fi | The Left Hand of Darkness | 2 | 2 (6 rows) ``` `rank()` keeps both fantasy books tied on 3 copies. `row_number()` would have kept exactly two rows per genre and dropped one of them arbitrarily. Literary has only one book with sales (Kafka's only order was cancelled), so it shows one row. ## Key takeaways - A window function computes over related rows but keeps every row; `OVER (PARTITION BY ... ORDER BY ... frame)` defines the window. - Window functions run after `WHERE`/`GROUP BY`/`HAVING`, so filter on them in an outer query or CTE. - `row_number`, `rank` and `dense_rank` differ only in tie handling; `rank` in a CTE plus `WHERE r <= n` solves top-N per group. - Adding `ORDER BY` makes aggregates cumulative; the default frame is `RANGE ... CURRENT ROW`, which includes peers, so state `ROWS` explicitly for running totals. - `lag`/`lead` compare a row with its neighbours; `last_value` needs a frame extended to `UNBOUNDED FOLLOWING`. ## Quiz 1. Three books cost 10, 10 and 12. What does rank() OVER (ORDER BY price) return for them? A. 1, 2, 3 B. 1, 1, 2 C. 1, 1, 3 D. 2, 2, 3 **Answer: C.** rank() gives tied rows the same rank and then skips, so the next rank is 3. dense_rank() would give 1, 1, 2 and row_number() 1, 2, 3. 2. Why does SELECT title, rank() OVER (ORDER BY price) AS r FROM books WHERE r <= 3 fail? A. WHERE is evaluated before window functions, so r does not exist yet; filter in an outer query or CTE B. rank() requires a PARTITION BY clause C. Window functions cannot be aliased D. LIMIT must be used instead of WHERE with window functions **Answer: A.** The logical order is FROM, WHERE, GROUP BY, HAVING, then window functions and SELECT. WHERE cannot see a value that has not been computed yet. 3. What frame does sum(total) OVER (ORDER BY ordered_on) use when no frame is written? A. Only the current row B. The whole partition C. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW D. RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all rows tied with the current one **Answer: D.** With ORDER BY and no explicit frame, the default is RANGE UNBOUNDED PRECEDING to CURRENT ROW, and RANGE treats peers (equal ordered_on) as part of the current row. Without ORDER BY the frame is the whole partition. 4. What does lag(ordered_on) OVER (PARTITION BY customer_id ORDER BY ordered_on) return for a customer's first order? A. The same date as ordered_on B. NULL C. The last order of the previous customer D. An error **Answer: B.** lag looks within the partition only. The first row has no previous row, so the result is NULL unless you pass a default as the third argument, e.g. lag(ordered_on, 1, ordered_on).