Window Functions

Window functions are one of PostgreSQL's most powerful features. They perform calculations across a set of rows that are related to the current row — without collapsing them into a single output row like GROUP BY does. Think of them as supercharged aggregates that keep the detail.

Basic Window Function

The OVER clause defines the window:

SELECT
  first_name,
  last_name,
  department_id,
  COUNT(*) OVER (PARTITION BY department_id) AS dept_size
FROM students;

This shows each student alongside how many students are in their department. Unlike GROUP BY, every row is preserved — you get the individual student data plus the department count.

PARTITION BY divides the data into groups (like GROUP BY). The window function operates within each partition.

ROW_NUMBER

Assign a unique number to each row:

SELECT
  first_name,
  last_name,
  ROW_NUMBER() OVER (ORDER BY enrollment_date) AS enrollment_order
FROM students;

This numbers students in order of when they enrolled. Useful for ranking and pagination.

-- Rank students within each department
SELECT
  first_name,
  last_name,
  department_id,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY gpa DESC) AS rank_in_dept
FROM students;

RANK and DENSE_RANK

RANK assigns the same number to tied values, then skips ranks:

SELECT
  first_name,
  last_name,
  gpa,
  RANK() OVER (ORDER BY gpa DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY gpa DESC) AS dense_rank
FROM students;

If two students are tied at rank 2, RANK gives the next student rank 4 (skipping 3). DENSE_RANK gives the next student rank 3 (no gap).

LAG and LEAD

Access values from previous or next rows:

SELECT
  first_name,
  last_name,
  enrollment_date,
  LAG(enrollment_date) OVER (ORDER BY enrollment_date) AS prev_enrollment,
  LEAD(enrollment_date) OVER (ORDER BY enrollment_date) AS next_enrollment
FROM students;

LAG gets the previous row's value, LEAD gets the next row's value. Great for comparing consecutive rows, like calculating growth between periods.

Running Totals

Combine window functions with frame clauses for running calculations:

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

The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines which rows are included in the calculation. This creates a running total that grows with each row.

NTILE

Divide rows into equal groups:

SELECT
  first_name,
  last_name,
  gpa,
  NTILE(4) OVER (ORDER BY gpa DESC) AS quartile
FROM students;

This assigns each student to one of four quartiles based on GPA. Very useful for statistical analysis and bucketing data.

Window Functions vs GROUP BY

The key difference:

• GROUP BY — Collapses rows. Each group becomes one output row.
• Window function — Preserves rows. Adds calculated values alongside each row.

Use GROUP BY when you want summary statistics. Use window functions when you want summary statistics alongside the original detail rows.