advancedAdvanced SQLPostgreSQL Specific100% Free

Window Functions in PostgreSQL

Definition

PostgreSQL window functions compute values across a 'window' of rows related to the current row using OVER(), enabling analytics without collapsing rows.

Introduction to Window Functions in PostgreSQL

PostgreSQL has one of the most complete and performant window function implementations of any database. Beyond the standard ROW_NUMBER, RANK, and DENSE_RANK, PostgreSQL offers FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, and full frame specification support (ROWS vs RANGE vs GROUPS).

Syntax

SQL

SELECT column,
  FUNC() OVER (
    PARTITION BY partition_col
    ORDER BY sort_col
    ROWS | RANGE | GROUPS
    BETWEEN frame_start AND frame_end
  )
FROM table;

Examples

NTILE — Percentile Bucketing

SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

NTILE(4) divides employees into 4 equal salary quartiles. Quartile 1 = top 25% earners.

Try in Playground

FIRST_VALUE and LAST_VALUE

SELECT name, department, salary,
  FIRST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS top_earner_in_dept
FROM employees;

FIRST_VALUE returns the first value in the window frame. Each employee row shows who the top earner in their department is.

Try in Playground

Common Mistakes

LAST_VALUE returning unexpected results — the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Window functions in WHERE (not allowed) — use a CTE or subquery

Not using PARTITION BY when separate calculations per group are needed

Frequently Asked Questions

What window functions are available in PostgreSQL?

PostgreSQL supports: Ranking functions (ROW_NUMBER, RANK, DENSE_RANK, NTILE), Value functions (FIRST_VALUE, LAST_VALUE, NTH_VALUE, LAG, LEAD), and Aggregate functions used as window functions (SUM, AVG, COUNT, MIN, MAX OVER()). All standard SQL window functions are supported.

What is the difference between ROWS and RANGE in window frame specification?

ROWS counts physical rows. RANGE counts logical values. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING always includes exactly the previous and next row. RANGE BETWEEN 1 PRECEDING AND CURRENT ROW includes all rows with values within 1 unit of the current row's value — this can include multiple rows for duplicate values.

Practice This in the SQL Playground

Write real queries, see live results, and master Window Functions in PostgreSQL hands-on. 100% free.

Open SQL Playground Browse All Lessons