⬛

PostgreSQL Window Functions

Complete reference for PostgreSQL window functions covering ranking, lag/lead access, running totals, and partition-based calculations using the OVER() clause. Every function includes PARTITION BY and ORDER BY examples, frame specification, and real-world query patterns. Updated for PostgreSQL 16.

12 functions

Jump to function

cume_dist dense_rank first_value lag last_value lead nth_value ntile percent_rank rank row_number sum (window)

What are PostgreSQL Window Functions?

PostgreSQL window functions compute results across a set of rows related to the current row without collapsing rows like aggregate functions do. They use the OVER() clause with optional PARTITION BY and ORDER BY to define the window frame. Common PostgreSQL window functions include row_number(), rank(), dense_rank(), lag(), lead(), first_value(), last_value(), nth_value(), and sum() OVER() for running totals.

cume_dist

PG 8.4+→ double precision

Returns the cumulative distribution of the current row: proportion of rows with values <= current row's value. Result is always in (0, 1].

DeveloperData Eng

Signature

cume_dist ( ) → double precision

Examples

sql

SELECT name, score, cume_dist() OVER (ORDER BY score) AS cdf FROM results;

→Fraction of rows scoring <= this row

sql

SELECT * FROM results WHERE cume_dist() OVER (ORDER BY score DESC) <= 0.1;

→Top 10% of scores

sql

SELECT employee_id, salary, round((cume_dist() OVER (ORDER BY salary) * 100)::numeric, 1) AS salary_percentile FROM employees;

→What percentage of employees earn at or below this salary

sql

SELECT product_id, revenue, cume_dist() OVER (PARTITION BY category ORDER BY revenue) AS category_cdf FROM sales;

→Cumulative revenue distribution within each category

⚠Anti-Pattern— Filtering on a window function result directly in WHERE

You cannot use cume_dist() (or any window function) directly in a WHERE clause. Wrap the query in a CTE or subquery first.

✓ Instead: WITH dist AS (SELECT *, cume_dist() OVER (ORDER BY lifetime_value DESC) AS cd FROM customers) SELECT * FROM dist WHERE cd <= 0.05;

Use `cume_dist() OVER (ORDER BY score DESC) <= 0.1` to select the top 10% without pre-computing the 90th percentile threshold. This adapts automatically as data changes.

example

SELECT * FROM customers WHERE cume_dist() OVER (ORDER BY lifetime_value DESC) <= 0.05;

→Top 5% of customers by lifetime value

dense_rank

PG 8.4+→ bigint

Returns the rank of the current row within its partition without gaps — tied rows share a rank, and the next rank is sequential (1, 2, 2, 3).

DeveloperDBAData Eng

Signature

dense_rank ( ) → bigint

Examples

sql

SELECT name, score, dense_rank() OVER (ORDER BY score DESC) AS rnk FROM contest;

→1, 2, 2, 3 (no gaps after ties)

sql

SELECT *, dense_rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr FROM employees;

→Dense rank within department

sql

SELECT customer_id, total_spend, dense_rank() OVER (ORDER BY total_spend DESC) AS spend_tier FROM customers;

→Tier 1 = highest spenders, no gaps between tiers

sql

WITH tiers AS (SELECT id, dense_rank() OVER (ORDER BY score DESC) AS tier FROM scores) SELECT * FROM tiers WHERE tier <= 3;

→All rows in the top 3 distinct score tiers

⚠Anti-Pattern— Confusing dense_rank() with rank() for competition rankings

In competition contexts (sports, contests) where skipping ranks after ties is expected, using dense_rank() gives misleading results. Two 2nd-place finishers should leave no 3rd place — use rank() there.

✓ Instead: SELECT name, score, rank() OVER (ORDER BY score DESC) AS place FROM leaderboard;

Use `dense_rank()` when you want clean tier numbers (tier 1, 2, 3...) with no gaps after ties. Useful for customer segmentation where multiple customers share the same tier level.

example

SELECT customer_id, dense_rank() OVER (ORDER BY total_spend DESC) AS spend_tier FROM customers;

→1, 2, 2, 3... clean tiers

first_value

PG 8.4+→ same as value

Returns the value of the expression evaluated at the first row of the window frame.

DeveloperData Eng

Signature

first_value ( value ) → same as value

Parameters

Parameter	Type	Description
value	any	Expression to evaluate at the first row

Examples

sql

SELECT name, salary, first_value(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS highest_earner FROM employees;

→Name of highest earner in each dept, shown on every row

sql

SELECT date, value, first_value(value) OVER (PARTITION BY product ORDER BY date) AS first_reading FROM measurements;

→First reading per product

sql

SELECT order_id, amount, first_value(amount) OVER (PARTITION BY customer_id ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_order_amount FROM orders;

→First order amount for each customer on every row

sql

SELECT id, score, score - first_value(score) OVER (ORDER BY recorded_at) AS change_from_baseline FROM test_scores;

→Score change relative to the first recorded score

⚠Anti-Pattern— Not specifying ORDER BY with first_value

Without ORDER BY in the OVER clause, the 'first' row is undefined — the database can pick any row. Always include ORDER BY to ensure deterministic results.

✓ Instead: SELECT name, first_value(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner FROM employees;

The default frame is `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`. For `first_value`, this usually does what you want. For `last_value`, you need to specify `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` to get the true last value.

example

SELECT id, value, last_value(value) OVER (PARTITION BY group_id ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS group_last FROM data;

→True last value per group on every row

lag

PG 8.4+→ same as value

Returns the value from a row offset rows before the current row in the partition, or the default value if there is no such row.

DeveloperData Eng

Signature

lag ( value [, offset integer [, default any]] ) → same as value

Parameters

Parameter	Type	Description
value	any	Expression to fetch from the lagged row
offset	integer	Number of rows to look back (default 1)
default	any	Value to return when there is no prior row (default NULL)

Examples

sql

SELECT date, revenue, lag(revenue) OVER (ORDER BY date) AS prev_revenue FROM daily_sales;

→Previous day's revenue in each row

sql

SELECT date, revenue - lag(revenue, 1, 0) OVER (ORDER BY date) AS change FROM daily_sales;

→Day-over-day revenue change (0 for first row instead of NULL)

sql

SELECT user_id, login_time, lag(login_time) OVER (PARTITION BY user_id ORDER BY login_time) AS prev_login FROM logins;

→Each user's previous login time

sql

SELECT date, close_price, round(100.0 * (close_price - lag(close_price) OVER (ORDER BY date)) / lag(close_price) OVER (ORDER BY date), 2) AS pct_change FROM stock_prices;

→Day-over-day percentage price change

⚠Anti-Pattern— Not providing a default for lag() on the first row

When there is no prior row, lag() returns NULL by default. This propagates NULL into calculations such as revenue differences. Supply a sensible default (often 0) as the third argument.

✓ Instead: SELECT date, revenue - lag(revenue, 1, 0) OVER (ORDER BY date) AS change FROM daily_sales;

Use `lag(value) OVER (PARTITION BY group ORDER BY date)` to compute period-over-period changes per group. Partition by user/product/region to compare each entity's value to its own prior period.

example

SELECT month, revenue, revenue - lag(revenue) OVER (ORDER BY month) AS mom_change, round(100.0 * (revenue - lag(revenue) OVER (ORDER BY month)) / lag(revenue) OVER (ORDER BY month), 1) AS pct_change FROM monthly_revenue;

→Month-over-month absolute and percent change

last_value

PG 8.4+→ same as value

Returns the value of the expression at the last row of the window frame. Requires explicit frame clause to see beyond the current row.

DeveloperData Eng

Signature

last_value ( value ) → same as value

Parameters

Parameter	Type	Description
value	any	Expression to evaluate at the last row

Examples

sql

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

→Highest earner name in each dept

sql

SELECT date, price, last_value(price) OVER (PARTITION BY ticker ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_price FROM stock_prices;

→Most recent price for each ticker on every row

sql

SELECT session_id, event_time, last_value(event_time) OVER (PARTITION BY session_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS session_end FROM events;

→Session end time on every event row

UNBOUNDED FOLLOWING required to see the true last value

sql

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

→(rows with dept's max salary in each row)

⚠Anti-Pattern— Omitting ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY, last_value sees only up to the current row, so it returns the current row's value — not the true last. Always specify the full frame explicitly.

✓ Instead: SELECT id, last_value(id) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_id FROM t;

Without `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`, `last_value` only sees up to the current row (the default frame). Always set the frame explicitly when using `last_value`.

example

SELECT id, last_value(id) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS max_id FROM t;

→Maximum id on every row

lead

PG 8.4+→ same as value

Returns the value from a row offset rows after the current row in the partition.

DeveloperData Eng

Signature

lead ( value [, offset integer [, default any]] ) → same as value

Parameters

Parameter	Type	Description
value	any	Expression to fetch from the leading row
offset	integer	Number of rows to look ahead (default 1)
default	any	Value to return when there is no next row (default NULL)

Examples

sql

SELECT event_time, lead(event_time) OVER (PARTITION BY session_id ORDER BY event_time) AS next_event FROM events;

→Time of next event in the same session

sql

SELECT id, status, lead(status) OVER (ORDER BY created_at) AS next_status FROM order_history;

→Shows what the next status was

sql

SELECT user_id, page, lead(page, 1, 'exit') OVER (PARTITION BY user_id ORDER BY event_time) AS next_page FROM pageviews;

→Next page visited, 'exit' if this was the last page

sql

SELECT date, forecast, lead(forecast, 7) OVER (ORDER BY date) AS forecast_next_week FROM forecasts;

→Compare today's forecast to the forecast 7 rows ahead

⚠Anti-Pattern— Not providing a default for lead() on the last row

lead() returns NULL when no following row exists (last row in partition). If NULL causes problems downstream (e.g. in duration calculations), provide a sensible default as the third argument.

✓ Instead: SELECT user_id, page, lead(page, 1, 'exit') OVER (PARTITION BY user_id ORDER BY event_time) AS next_page FROM pageviews;

Use `lead(event_time) - event_time` to compute the duration between consecutive events in a session. Where `lead()` returns NULL (last event in session), the session ended there.

example

SELECT session_id, event_time, lead(event_time) OVER (PARTITION BY session_id ORDER BY event_time) - event_time AS time_to_next FROM events;

→Duration between consecutive events per session

nth_value

PG 8.4+→ same as value

Returns the value at the nth row of the window frame (1-based). Returns NULL if the frame has fewer than n rows.

DeveloperData Eng

Signature

nth_value ( value, n integer ) → same as value

Parameters

Parameter	Type	Description
value	any	Expression to evaluate
n	integer	1-based row position within the window frame

Examples

sql

SELECT name, salary, nth_value(salary, 2) OVER (PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest FROM employees;

→Second highest salary in dept

sql

SELECT product_id, revenue, nth_value(product_id, 3) OVER (ORDER BY revenue DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_best FROM products;

→Third best-selling product id on every row

sql

SELECT race_id, driver, lap_time, nth_value(lap_time, 1) OVER (PARTITION BY race_id ORDER BY lap_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS fastest_lap FROM lap_times;

→Fastest lap time per race shown on every row

sql

SELECT category, product, price, nth_value(price, 2) OVER (PARTITION BY category ORDER BY price ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_cheapest FROM products;

→Second cheapest price per category, NULL if only one product

⚠Anti-Pattern— Omitting ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING with nth_value

Like last_value, nth_value is frame-sensitive. With the default frame (up to current row), it returns NULL for positions beyond the current row. Always specify the full frame to get consistent results across all rows.

✓ Instead: SELECT name, nth_value(salary, 2) OVER (PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS second_highest FROM employees;

Combine with `ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING` to see any row's value relative to the whole partition at once.

example

SELECT product_id, nth_value(product_id, 3) OVER (ORDER BY sales DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_top_seller FROM products;

→Third best-selling product id on every row

Common Gotchas

⚠

strpos() returns 0 (not -1 or NULL) when substring not found

strpos() returns 0 for 'not found', not NULL or -1 like many languages. Writing > -1 will match everything.

ntile

PG 8.4+→ integer

Divides the rows in the partition into num_buckets groups as evenly as possible and assigns each row a bucket number (1-based).

DeveloperData Eng

Signature

ntile ( num_buckets integer ) → integer

Parameters

Parameter	Type	Description
num_buckets	integer	Number of buckets to divide rows into

Examples

sql

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

→1=lowest 25%, 4=top 25%

sql

SELECT customer_id, ntile(10) OVER (ORDER BY total_spend DESC) AS decile FROM customers;

→Decile 1 = top 10% spenders

sql

SELECT id, score, CASE ntile(4) OVER (ORDER BY score) WHEN 1 THEN 'Low' WHEN 2 THEN 'Mid-Low' WHEN 3 THEN 'Mid-High' WHEN 4 THEN 'High' END AS tier FROM test_results;

→Human-readable quartile labels

sql

SELECT product_id, revenue, ntile(5) OVER (PARTITION BY category ORDER BY revenue DESC) AS quintile FROM products;

→Revenue quintile per product category

⚠Anti-Pattern— Using ntile() to find exact percentile thresholds

ntile() assigns bucket numbers but does not tell you the boundary values. For exact percentile thresholds (e.g. the 90th-percentile salary value), use percentile_cont() instead.

✓ Instead: SELECT percentile_cont(0.9) WITHIN GROUP (ORDER BY salary) FROM employees;

`ntile(4)` gives Q1–Q4 labels. Combine with `CASE` to add meaningful labels: `CASE ntile(4) OVER (ORDER BY revenue) WHEN 1 THEN 'Low' WHEN 4 THEN 'High' ELSE 'Mid' END`.

example

SELECT customer_id, revenue, ntile(4) OVER (ORDER BY revenue) AS revenue_quartile FROM customers;

→Customer revenue quartile assignment

percent_rank

PG 8.4+→ double precision

Returns the relative rank of the current row as a fraction between 0 and 1: (rank - 1) / (total rows - 1).

DeveloperData Eng

Signature

percent_rank ( ) → double precision

Examples

sql

SELECT name, score, percent_rank() OVER (ORDER BY score) AS pct_rank FROM results;

→0.0 = lowest, 1.0 = highest

sql

SELECT name, percent_rank() OVER (ORDER BY score) * 100 AS percentile FROM results;

→Percentile 0–100

sql

SELECT employee_id, salary, round((percent_rank() OVER (PARTITION BY dept ORDER BY salary) * 100)::numeric, 1) AS dept_percentile FROM employees;

→Salary percentile within each department

sql

SELECT id, score, CASE WHEN percent_rank() OVER (ORDER BY score DESC) <= 0.1 THEN 'Top 10%' ELSE 'Other' END AS tier FROM test_scores;

→Labels the top 10% of scorers

⚠Anti-Pattern— Confusing percent_rank with cume_dist

percent_rank() is (rank-1)/(N-1) and the first row is always 0.0. cume_dist() is rank/N and the last row is always 1.0, with the first row > 0. They measure different things — choose based on whether you want the fraction of rows below or at/below the current row.

✓ Instead: SELECT name, cume_dist() OVER (ORDER BY score) AS cumulative_fraction FROM results;

`percent_rank() * 100` gives the percentile rank (0–100). The first row is always 0.0, the last always 1.0 (or 100%).

example

SELECT name, round((percent_rank() OVER (ORDER BY score) * 100)::numeric, 1) AS percentile FROM test_scores;

→Percentile rank 0.0–100.0

rank

PG 8.4+→ bigint

Returns the rank of the current row within its partition, with gaps after ties (1, 2, 2, 4 for two tied rows).

DeveloperDBAData Eng

Signature

rank ( ) → bigint

Examples

sql

SELECT name, score, rank() OVER (ORDER BY score DESC) AS rnk FROM contest;

→Ties share the same rank; next rank is skipped

sql

SELECT *, rank() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank FROM employees;

→Ranked within each department, with gaps

sql

SELECT product_id, sales, rank() OVER (ORDER BY sales DESC) AS sales_rank FROM products WHERE rank() OVER (ORDER BY sales DESC) <= 5;

→ERROR — use a CTE instead to filter on rank

sql

WITH ranked AS (SELECT product_id, sales, rank() OVER (ORDER BY sales DESC) AS rnk FROM products) SELECT * FROM ranked WHERE rnk <= 5;

→Top 5 products by sales, ties included

⚠Anti-Pattern— Using rank() when dense_rank() is needed

rank() produces gaps after ties (1, 2, 2, 4). If consumers of your output expect contiguous tier numbers, this causes confusion. Use dense_rank() for gap-free ranking.

✓ Instead: SELECT name, score, dense_rank() OVER (ORDER BY score DESC) AS rnk FROM contest;

Use `rank()` when two tied second-place entries should both be rank 2 with no rank 3 (2, 2, 4). If you want 2, 2, 3 instead, use `dense_rank()`.

example

SELECT name, score, rank() OVER (ORDER BY score DESC) AS place FROM leaderboard;

→1, 2, 2, 4... (gap after ties)

row_number

PG 8.4+→ bigint

Returns the sequential row number within the current partition, starting from 1. No ties — every row gets a unique number.

DeveloperDBAData Eng

Signature

row_number ( ) → bigint

Examples

sql

SELECT name, salary, row_number() OVER (ORDER BY salary DESC) AS rn FROM employees;

→Rank 1, 2, 3... by salary (no ties)

sql

SELECT *, row_number() OVER (PARTITION BY dept ORDER BY salary DESC) AS dept_rank FROM employees;

→Rank within each department

sql

WITH deduped AS (SELECT *, row_number() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn FROM users) SELECT * FROM deduped WHERE rn = 1;

→Most recent row per email address

sql

SELECT *, row_number() OVER (PARTITION BY customer_id ORDER BY order_date) AS order_seq FROM orders;

→Sequential order number per customer (1st, 2nd, 3rd...)

⚠Anti-Pattern— Using row_number() in WHERE directly

You cannot reference a window function alias in a WHERE clause — it is evaluated after SELECT. Wrapping in a subquery or CTE is required.

✓ Instead: WITH numbered AS (SELECT *, row_number() OVER (PARTITION BY key ORDER BY updated_at DESC) AS rn FROM t) SELECT * FROM numbered WHERE rn = 1;

Use `row_number() OVER (PARTITION BY key ORDER BY updated_at DESC)` to number duplicates, then filter `WHERE rn = 1` to keep only the most recent row per key — a clean deduplication pattern.

example

DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id, row_number() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn FROM t) x WHERE rn > 1);

→Removes duplicate emails, keeping the newest

sum (window)

PG 8.4+→ same as aggregate

Any aggregate function can be used as a window function. sum() over a window computes cumulative or rolling sums.

DeveloperData Eng

Signature

sum ( expression ) OVER ([PARTITION BY ...] ORDER BY ... [frame_clause]) → numeric

Parameters

Parameter	Type	Description
expression	numeric	Value to sum

Examples

sql

SELECT date, revenue, sum(revenue) OVER (ORDER BY date) AS running_total FROM daily_sales;

→Cumulative sum of revenue

sql

SELECT date, revenue, sum(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d FROM daily_sales;

→7-day rolling sum

sql

SELECT dept, name, salary, sum(salary) OVER (PARTITION BY dept) AS dept_total FROM employees;

→Department total on every row

Running total showing cumulative revenue

sql

SELECT month, revenue, SUM(revenue) OVER (ORDER BY month ROWS UNBOUNDED PRECEDING) AS running_total FROM monthly_sales;

→Jan:1000|1000, Feb:1500|2500, Mar:900|3400

⚠Anti-Pattern— Using a correlated subquery instead of SUM() OVER for running totals

A correlated subquery to compute a running total rescans the table for every row (O(n²)). Use SUM() OVER with an ORDER BY clause instead — it computes the running total in a single pass.

✓ Instead: SELECT date, revenue, sum(revenue) OVER (ORDER BY date) AS running_total FROM daily_sales;

Use `ROWS BETWEEN N PRECEDING AND CURRENT ROW` for a strictly row-count-based rolling window. `RANGE` uses value proximity, which can include more rows for tied values. For time-series rolling windows, `ROWS` is usually what you want.

example

SELECT date, avg(value) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS rolling_30d_avg FROM metrics;

→30-day rolling average

PostgreSQL Window Functions

Jump to function

What are PostgreSQL Window Functions?

cume_dist

Signature

Examples

dense_rank

Signature

Examples

first_value

Signature

Parameters

Examples

lag

Signature

Parameters

Examples

last_value

Signature

Parameters

Examples

lead

Signature

Parameters

Examples

nth_value

Signature

Parameters

Examples

Common Gotchas

ntile

Signature

Parameters

Examples

percent_rank

Signature

Examples

rank

Signature

Examples

row_number

Signature

Examples

sum (window)

Signature

Parameters

Examples

Related PostgreSQL Categories