Step 07 of 13

Window functions

Use OVER, PARTITION BY and frames to compute rankings, running totals, moving averages and row-to-row comparisons without collapsing rows.

18 min 7 sections

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).

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.

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

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.
Using copies sold in non-cancelled orders, list the top two books per genre. If books tie, include all of them.

Show 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.

Check yourself

Quiz

0/4 correct · 4 left

1.Three books cost 10, 10 and 12. What does rank() OVER (ORDER BY price) return for them?
2.Why does SELECT title, rank() OVER (ORDER BY price) AS r FROM books WHERE r <= 3 fail?
3.What frame does sum(total) OVER (ORDER BY ordered_on) use when no frame is written?
4.What does lag(ordered_on) OVER (PARTITION BY customer_id ORDER BY ordered_on) return for a customer's first order?

Finished reading?

Mark it complete to fill in your roadmap.

PreviousSubqueries & CTEs NextModifying data

#The OVER clause

#Where window functions run

#Ranking functions

#Top N per group

#Running totals and frames

#The default frame and peers

#Comparing rows: LAG, LEAD and friends

#Combining windows in one query

#Practice

#Key takeaways

Quiz

The OVER clause

Where window functions run

Ranking functions

Top N per group

Running totals and frames

The default frame and peers

Comparing rows: LAG, LEAD and friends

Combining windows in one query

Practice

Key takeaways