Window Functions
Calculate across rows

By now you can filter (WHERE), aggregate (GROUP BY with SUM, AVG, COUNT), join multiple tables, and reach for subqueries and CTEs to compose logic. Every aggregate you have written so far shares one trait: it collapses. Ten employees in a department become a single row holding their average salary. The individual rows are gone.

That is exactly the limitation window functions remove. Maya's first ticket reads: "show every employee, their salary, and the average salary of their department, on the same row." She tries GROUP BY and gets stuck. Grouping destroys the per-employee rows, so she would need a self-join or a correlated subquery to glue the average back on. A window function answers it directly.

The mechanism is a single new clause, OVER(...), bolted onto a function call. When the database sees OVER, it switches that function from "collapse mode" into "window mode." Everything in this module is really just learning what you can put inside those parentheses.

A window function is any aggregate-or-window function followed by OVER (...). Three optional pieces live inside the parentheses, and each one shapes the window differently.

Maya starts with the simplest possible window: empty parentheses. OVER () means "the window is the entire result set." Every row sees every other row.

SELECT name, dept_id, salary,
       AVG(salary) OVER ()::numeric(10,0) AS company_avg
FROM employees;

-- every row keeps its identity AND gets the same company-wide average
 name  | dept_id | salary | company_avg
-------+---------+--------+-------------
 Alice |      10 | 185000 |      119167
 Bob   |      10 | 120000 |      119167
 Carol |      20 | 135000 |      119167
 Dan   |      20 |  98000 |      119167
 Eve   |      30 | 105000 |      119167
 Frank |  (null) |  72000 |      119167

Six rows in, six rows out. The average (185000+120000+135000+98000+105000+72000)/6 = 119167 is computed once and stamped onto every row. No GROUP BY appeared anywhere, and we did not lose a single employee.

Now add PARTITION BY dept_id. This splits the rows into independent groups (partitions) and computes the average separately within each one, but, crucially, still emits one row per employee. That is what Maya's ticket actually asked for.

SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id)::numeric(10,0) AS dept_avg
FROM employees
ORDER BY dept_id, salary DESC;

 name  | dept_id | salary | dept_avg
-------+---------+--------+----------
 Alice |      10 | 185000 |   152500   <- (185000+120000)/2
 Bob   |      10 | 120000 |   152500
 Carol |      20 | 135000 |   116500   <- (135000+98000)/2
 Dan   |      20 |  98000 |   116500
 Eve   |      30 | 105000 |   105000   <- only one row in dept 30
 Frank |  (null) |  72000 |    72000   <- NULL is its own partition

Look at what Maya gets: salary and dept_avg sitting side by side on every row. Alice can instantly be compared to her department's mean (185000 vs 152500) without losing the row. That side-by-side shape is the whole point, and it is impossible with a plain GROUP BY.

Let us make it razor-sharp. Both clauses carve the data into the same groups using the same columns. The difference is what happens to the rows afterward.

A practical tell Maya now uses: if your question contains the word "each" pointing at individual records ("show each order with its running total," "rank each employee within their department"), you almost certainly want a window function, not GROUP BY.

Once a window has an ORDER BY inside it, you can number the rows. Three functions do this, and they differ only in how they treat ties. Knowing the difference cold is worth the few minutes it takes.

Here is the classic task: rank employees by salary within each department, highest first. The PARTITION BY restarts the numbering for every department; the ORDER BY decides who is rank 1.

SELECT name, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drnk
FROM employees
ORDER BY dept_id, salary DESC;

To see the tie behavior, imagine department 20 had a tie, say Carol and a second employee both earn 135000. This is the difference the three functions produce on that tie:

Read the bottom row carefully. That is the whole lesson. After a two-way tie: ROW_NUMBER just kept counting (3). RANK skipped 2 and jumped to 3 (the gap). DENSE_RANK stayed contiguous at 2 (no gap).

Maya's next ticket: "Find the highest-paid employee in each department." The clean answer is the canonical window-function pattern: number the rows, then keep the ones you want. But there is a catch that trips people, and understanding it is the real lesson.

WITH ranked AS (
  SELECT name, dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id
                            ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, dept_id, salary
FROM ranked
WHERE rn = 1;          -- top 1; use rn <= 2 for top 2, etc.

 name  | dept_id | salary
-------+---------+--------
 Alice |      10 | 185000
 Carol |      20 | 135000
 Eve   |      30 | 105000
 Frank |  (null) |  72000

The structure is always the same: compute ROW_NUMBER in an inner query (a CTE or subquery), then filter on that number in an outer query. Change rn = 1 to rn <= 2 and you get the top two per department, and so on.

Why you must wrap it

The obvious-looking shortcut does not work, and here is the exact reason:

-- ERROR: window functions are not allowed in WHERE
SELECT name, dept_id, salary
FROM employees
WHERE ROW_NUMBER() OVER (PARTITION BY dept_id
                       ORDER BY salary DESC) = 1;

Recall the logical order of operations from Module 00: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Window functions are computed at the SELECT stage (step 5) because they need the rows already filtered and grouped before they can rank them. But WHERE runs at step 2, before the window function exists. You cannot filter on a value that has not been calculated yet.

Sometimes a row needs to look at its neighbor. When Maya is asked how each order compares to the one before it, this is the tool. LAG fetches a value from a previous row; LEAD fetches from a following row, both relative to the window's ORDER BY. That makes period-over-period comparisons trivial.

Let us use orders, sorted by order_date. Each row should show the previous order's amount and the change from it.

SELECT order_id, order_date, amount,
       LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY order_date) AS delta
FROM orders
ORDER BY order_date;

 order_id | order_date | amount | prev_amount | delta
----------+------------+--------+-------------+--------
     1001 | 2024-01-05 |    500 |      (null) | (null)   <- no prior row
     1002 | 2024-01-09 |    800 |         500 |    300
     1003 | 2024-01-15 |    300 |         800 |   -300
     1004 | 2024-01-22 |    950 |         300 |    650

The very first row has no predecessor, so LAG returns NULL, and any arithmetic with that NULL is also NULL (recall Module 00's three-valued logic). LEAD(amount) works identically but looks forward, which is handy for "what is the next order" style questions.

You can partition too. LAG(amount) OVER (PARTITION BY emp_id ORDER BY order_date) compares each order to that employee's own previous order. The LAG resets at every employee boundary, never bleeding across partitions.

An aggregate inside a window with an ORDER BY becomes a running aggregate. A running total of order amounts (the cumulative sum as time moves forward) is the textbook example, and the next thing finance asks Maya for.

SELECT order_id, order_date, amount,
       SUM(amount) OVER (ORDER BY order_date
                          ROWS BETWEEN UNBOUNDED PRECEDING
                                   AND CURRENT ROW) AS running_total
FROM orders
ORDER BY order_date;

 order_id | order_date | amount | running_total
----------+------------+--------+---------------
     1001 | 2024-01-05 |    500 |           500
     1002 | 2024-01-09 |    800 |          1300
     1003 | 2024-01-15 |    300 |          1600
     1004 | 2024-01-22 |    950 |          2550

The new piece is the frame clause: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. It defines exactly which rows in the ordered window contribute to each row's calculation. Here: "every row from the very start of the partition up to and including the current one." So row 3's total covers rows 1–3, giving 1600.

You can build other windows by changing the frame bounds:

ROWS vs RANGE, the surprise

The frame can count in two units, and confusing them is a genuine trap. ROWS counts physical rows, "the 2 rows before this one," full stop. RANGE counts by value of the ORDER BY key: it includes all peer rows that share the current row's ordering value, treating them as a single logical block.

A moving average (say, the average of the last three orders) is just a ROWS frame around AVG: AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). The frame is what turns a static aggregate into a sliding one.

Beyond ranking and offsets, a few more functions reach into the window to pull out positional values or to slice rows into buckets.

SELECT name, dept_id, salary,
       FIRST_VALUE(name) OVER (PARTITION BY dept_id
                              ORDER BY salary DESC) AS dept_top_earner,
       NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

For true statistical percentiles, PostgreSQL offers PERCENT_RANK() and CUME_DIST() as window functions, plus the ordered-set aggregates PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) for an interpolated median. NTILE is the quick, good-enough bucketing tool for most reporting.

Step into Maya's seat. Use the same three-table schema from Module 00, and add a handful of orders rows with varied order_date and amount values (include at least two orders on the same date, since you will want that for the ROWS-vs-RANGE task). Write each query before peeking at any hint.

• Show every employee with their salary and the company-wide average on the same row, using AVG(...) OVER (). Confirm row count stays at 6.
• Add a dept_avg column with PARTITION BY dept_id, and a third column salary - dept_avg showing how far each person is above or below their department mean.
• Produce ROW_NUMBER, RANK, and DENSE_RANK for employees ordered by salary descending (whole company). Then introduce a salary tie and watch the three columns diverge.
• Use the CTE + ROW_NUMBER pattern to return the highest-paid employee per department. Then change it to the top 2.
• Try filtering on ROW_NUMBER() ... = 1 directly in WHERE, watch it error, and explain to yourself why (which execution stage).
• On orders ordered by date, add prev_amount via LAG and delta = amount - prev_amount. Then use LAG(amount, 1, 0) so the first row shows 0 instead of NULL.
• Build a running total of amount with an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame. Then remove the frame entirely and compare the two same-date rows, observing the RANGE default difference.
• Compute a 3-order moving average of amount with ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Then try LAST_VALUE and reproduce the default-frame trap before fixing it with a full frame.