Window Functions

Window functions perform calculations across a set of table rows related to the current row, without collapsing rows into groups as GROUP BY does. The OVER clause defines the window: PARTITION BY divides rows into independent groups, and ORDER BY determines the order within each partition. Ranking functions — ROW_NUMBER, RANK, DENSE_RANK, and NTILE — assign a rank to each row. Offset functions — LAG and LEAD — access values from preceding or following rows without a self-join. Aggregate functions like SUM and AVG can also be used as window functions, enabling running totals and moving averages. The ROWS or RANGE frame specification within OVER controls exactly which rows are included in each window calculation.

Example

-- ROW_NUMBER — unique rank within each partition
SELECT
  id, user_id, total, created_at,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_seq
FROM orders;

-- RANK vs DENSE_RANK (handle ties differently)
SELECT
  name, score,
  RANK()       OVER (ORDER BY score DESC) AS rank_with_gaps,
  DENSE_RANK() OVER (ORDER BY score DESC) AS rank_no_gaps
FROM leaderboard;

-- Running total with SUM window function
SELECT
  created_at::DATE  AS order_date,
  total,
  SUM(total) OVER (ORDER BY created_at
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                  ) AS running_total
FROM orders
ORDER BY created_at;

-- LAG/LEAD — compare with previous/next row
SELECT
  month,
  revenue,
  LAG(revenue)  OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS month_over_month
FROM monthly_revenue;

-- NTILE — divide rows into 4 quartiles
SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) AS quartile
FROM students;

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned