Window functions

conceptThe concept

f(...) OVER (PARTITION BY a ORDER BY b) evaluates f for every row over the rows in the same partition. Without ORDER BY the window is the whole partition; with ORDER BY the default frame is "from the start of the partition up to the current row and its peers", which is exactly a running total.

Ranking: row_number() numbers rows 1, 2, 3 with ties broken arbitrarily; rank() gives ties the same number and leaves gaps (1, 1, 3); dense_rank() does not leave gaps (1, 1, 2). ntile(4) splits into quartiles. Offsets: lag(x) and lead(x) read the previous and next row; first_value, last_value and nth_value read inside the frame.

The frame can be set explicitly: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is a 7-row moving window; RANGE BETWEEN interval '7 days' PRECEDING AND CURRENT ROW is a time-based window. Any aggregate (sum, avg, count) becomes a window function when you add OVER.

PostgreSQL 19+ lag, lead, first_value, last_value and nth_value accept IGNORE NULLS, so "carry the last known price forward" becomes last_value(price) IGNORE NULLS OVER (ORDER BY day). On 18 (this course's engine) you still need the count(price) OVER grouping trick.

whyWhy it matters

Without window functions, "each order with the customer's previous order date" or "share of category revenue" needs self-joins or correlated subqueries that are slower and harder to read. With them it is one pass over sorted data.

Use caseReal-world scenario

The growth team wants monthly revenue with a running year-to-date total, month-over-month change and a 3-month moving average. One query, one sort.

Try itExamples

Run and edit these in the interactive lesson.

SELECT category, name, price,
       row_number() OVER w AS row_number,
       rank()       OVER w AS rank,
       dense_rank() OVER w AS dense_rank
FROM products
WHERE category = 'books'
WINDOW w AS (PARTITION BY category ORDER BY price DESC)
ORDER BY price DESC
LIMIT 8;

WITH monthly AS (
  SELECT date_trunc('month', created_at)::date AS month, sum(total) AS revenue
  FROM orders
  WHERE status <> 'cancelled'
  GROUP BY 1
)
SELECT month, revenue,
       sum(revenue) OVER (PARTITION BY extract(year FROM month) ORDER BY month) AS ytd,
       revenue - lag(revenue) OVER (ORDER BY month) AS vs_prev_month,
       round(avg(revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3m,
       round(100 * revenue / sum(revenue) OVER (), 2) AS pct_of_all
FROM monthly
ORDER BY month;

SELECT customer_id, id AS order_id, created_at::date AS day,
       created_at - lag(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS since_previous
FROM orders
WHERE customer_id = 42
ORDER BY created_at
LIMIT 8;

With ORDER BY, the default frame ends at the current row, so last_value returns the current row. Extend the frame to see the real last value.

SELECT name, salary,
       last_value(name) OVER (ORDER BY salary) AS default_frame,
       last_value(name) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS full_frame
FROM employees
WHERE dept = 'Support'
ORDER BY salary;

SELECT *
FROM (
  SELECT dept, name, salary,
         round(100.0 * salary / sum(salary) OVER (PARTITION BY dept), 1) AS pct_of_dept
  FROM employees
) s
WHERE pct_of_dept > 25
ORDER BY dept, pct_of_dept DESC;

internalsUnder the hood

Window functions run after WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. The plan shows a WindowAgg node fed by a Sort on the PARTITION BY and ORDER BY keys, unless an index already provides that order. That is also why you cannot filter on a window result in WHERE; wrap it in a subquery or CTE.

Avoid / preferDo this, not that

SELECT id, row_number() OVER (ORDER BY total DESC) AS rn
FROM orders WHERE rn <= 10;

SELECT * FROM (
  SELECT id, row_number() OVER (ORDER BY total DESC) AS rn FROM orders
) t WHERE rn <= 10;

SELECT name, last_value(name) OVER (PARTITION BY dept ORDER BY salary) FROM employees;

SELECT name, last_value(name) OVER (PARTITION BY dept ORDER BY salary
       ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) FROM employees;

PitfallsCommon mistakes

• Using row_number() when ties should share a position. Pick rank or dense_rank deliberately.
• Expecting last_value or a windowed avg to cover the whole partition while an ORDER BY is present. Set the frame explicitly.
• Trying to filter on a window function in WHERE or HAVING. Compute it in a subquery or CTE first.

LimitsWhen not to use it

If you only need one row per group, GROUP BY or DISTINCT ON is simpler and often faster. Window functions keep every input row, which is what you want only when the detail rows matter.

SecuritySecurity notes

Rankings, percentiles and differences from an average reveal individual values even without names. Give reporting roles views that aggregate sensitive columns such as salary, not the raw table.

ChallengeTry it yourself

SELECT dept, name, salary,
       round(avg(salary) OVER w) AS dept_avg,
       salary - round(avg(salary) OVER w) AS diff
FROM employees
WINDOW w AS (PARTITION BY dept)
ORDER BY dept, salary DESC;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• Window functions tutorial
• Window function calls