PostgreSQL Window Functions: A Detailed Guide

Q: Can multiple window functions share one OVER clause?

Yes. PostgreSQL supports the WINDOW clause alias: WINDOW w AS (PARTITION BY ...) , then write OVER w in multiple functions.

1. What You'll Learn

The OVER clause and execution model of window functions
PARTITION BY for partitioning and ORDER BY for ordering
The three frame specs: ROWS / RANGE / GROUPS
Ranking functions: ROW_NUMBER / RANK / DENSE_RANK / NTILE
Offset functions: LAG / LEAD / FIRST_VALUE / LAST_VALUE / NTH_VALUE
Running sums and moving averages
The fundamental difference between window and aggregate functions

2. The Story

Charlie is a growth analyst at a SaaS platform. The product manager asked him to produce a user retention report:

Daily new-user count
Day 7 retention rate
Day 30 retention rate
Each user's signup rank (the Nth person who registered on the same day)

The traditional approach needs several SQL statements plus temporary tables. After learning window functions, Charlie gets all the stats in a single SQL statement—no GROUP BY to collapse rows; every row keeps its original data alongside the computed result.

3. Concept: Window Function Basics

(1) What Is a Window Function

A window function computes over a set of related rows (the "window") but does not collapse rows—every row gets a result back. This is its biggest difference from aggregate functions.

Feature	Aggregate function	Window function
Row count	Many rows → one row	Row count unchanged
Syntax	`SUM(col)`	`SUM(col) OVER (...)`
GROUP BY	Required	Not required
Keeps original columns	No	Yes
Typical use	Summary statistics	Ranking, offset, running totals

(2) The OVER Clause Structure

SQL

function_name() OVER (
  [PARTITION BY expr]
  [ORDER BY expr [ASC|DESC] [NULLS FIRST|NULLS LAST]]
  [frame_clause]
)

flowchart TD
    A[OVER] --> B[PARTITION BY]
    B --> C[ORDER BY]
    C --> D[Frame Clause]
    D --> E{ROWS / RANGE / GROUPS}
    E --> F[ROWS BETWEEN ... AND ...]
    E --> G[RANGE BETWEEN ... AND ...]
    E --> H[GROUPS BETWEEN ... AND ...]
    B -.->|optional| C
    C -.->|optional| D
    style A fill:#e1f5fe
    style B fill:#fff9c4
    style C fill:#fff9c4
    style D fill:#c8e6c9

▶ Example: The Simplest Window Function

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER () AS total_all
FROM orders;

TEXT

 order_id | customer_id | amount | total_all
----------+-------------+--------+-----------
      101 |           1 |  15000 |   750000
      102 |           1 |   8000 |   750000
      201 |           2 |  25000 |   750000

Every row returns the table-wide SUM, and the row count is unchanged.

4. Concept: PARTITION BY and ORDER BY

(1) PARTITION BY for Partitioning

PARTITION BY splits the data into independent "partitions," and the window function computes within each partition separately.

Clause	Effect	Analogy
`PARTITION BY col`	Partition by column	Like GROUP BY, but without collapsing
No PARTITION BY	Whole table is one partition	Like GROUP BY with no grouping column

▶ Example: Summing Partitioned by Customer

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders
ORDER BY customer_id, order_id;

TEXT

 order_id | customer_id | amount | customer_total
----------+-------------+--------+----------------
      101 |           1 |  15000 |          23000
      102 |           1 |   8000 |          23000
      201 |           2 |  25000 |          25000
      301 |           3 |  12000 |          57000
      302 |           3 |  45000 |          57000

(2) ORDER BY for Ordering

ORDER BY determines the row ordering within a partition and is essential for ranking and offset functions.

Scenario	Needs ORDER BY?	Reason
ROW_NUMBER / RANK	Required	Ranking depends on order
LAG / LEAD	Required	Previous/next rows depend on order
SUM() OVER (PARTITION BY)	Optional	Without order, computes partition total
FIRST_VALUE / LAST_VALUE	Required	First/last value depends on order

▶ Example: Ranking by Amount

SQL

SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn_desc
FROM orders;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(3) Combining PARTITION BY + ORDER BY

Partition first, then order; the ranking is computed independently within each partition.

▶ Example: Order Amount Ranking per Customer

SQL

SELECT
  order_id,
  customer_id,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY amount DESC
  ) AS rank_in_customer
FROM orders;

TEXT

 order_id | customer_id | amount | rank_in_customer
----------+-------------+--------+------------------
      102 |           1 |   8000 |                2
      101 |           1 |  15000 |                1
      201 |           2 |  25000 |                1
      302 |           3 |  45000 |                1
      301 |           3 |  12000 |                2

5. Concept: Frame Clause

(1) The Three Frame Types

The frame clause decides which rows a window function "sees" for its calculation.

Frame type	Boundary based on	Best for
ROWS	Physical row offset	Precise row control, e.g. "the previous 3 rows"
RANGE	Logical value offset	Rows with the same value grouped, e.g. "same amount"
GROUPS	Same-value group offset	PostgreSQL-specific; groups by ORDER BY value

(2) Frame Boundary Keywords

Keyword	Meaning
`UNBOUNDED PRECEDING`	First row of the partition
`UNBOUNDED FOLLOWING`	Last row of the partition
`CURRENT ROW`	The current row
`N PRECEDING`	N rows / values before
`N FOLLOWING`	N rows / values after

(3) Default Frame Rules

Has ORDER BY?	Default frame
Yes	`RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`
No	`ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`

▶ Example: ROWS Frame Running Sum

SQL

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

TEXT

 order_date  | amount | running_sum
-------------+--------+-------------
 2025-01-01  |   5000 |        5000
 2025-01-02  |   8000 |       13000
 2025-01-03  |   3000 |       16000
 2025-01-04  |  12000 |       28000

▶ Example: 3-Row Moving Average with ROWS

SQL

SELECT
  order_date,
  amount,
  ROUND(AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ), 2) AS moving_avg_3
FROM daily_sales;

TEXT

  result  
----------
   42.50
(1 row)

▶ Example: RANGE Frame over a Date Interval

SQL

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
  ) AS sum_last_7_days
FROM daily_sales;

TEXT

  result  
----------
   42.50
(1 row)

6. Concept: Ranking Functions

(1) The Four Ranking Functions Compared

Function	Same-value handling	Output	Consecutive?
ROW_NUMBER	Strictly increasing	1,2,3,4	Yes
RANK	Same rank, skips	1,1,3,4	No
DENSE_RANK	Same rank, no skip	1,1,2,3	Yes
NTILE(N)	Split into N groups	1,1,2,2,3,3	—

▶ Example: ROW_NUMBER vs RANK vs DENSE_RANK

SQL

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS rn,
  RANK()       OVER (ORDER BY score DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY score DESC) AS drnk
FROM students;

TEXT

 name   | score | rn | rnk | drnk
--------+-------+----+-----+------
 Alice  |    95 |  1 |   1 |    1
 Bob    |    90 |  2 |   2 |    2
 Charlie|    90 |  3 |   2 |    2
 Dave   |    85 |  4 |   4 |    3

(2) NTILE Grouping

NTILE(N) divides ordered rows into N roughly equal groups, commonly used for quartile analysis.

▶ Example: Splitting Customers into 4 Groups by Spend

SQL

SELECT
  customer_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent DESC) AS quartile
FROM customer_summary;

TEXT

 customer_id | total_spent | quartile
-------------+-------------+----------
          12 |      500000 |        1
           5 |      350000 |        1
           8 |      280000 |        2
          19 |      150000 |        2
           3 |       90000 |        3
          22 |       60000 |        3
          41 |       25000 |        4
          15 |        5000 |        4

7. Concept: Offset and Value Functions

(1) Offset Function Reference

Function	Effect	Typical use
LAG(col, N, default)	Value of the N-th row before current	Period-over-period growth
LEAD(col, N, default)	Value of the N-th row after current	Forecast, comparison
FIRST_VALUE(col)	First value in the window	First order amount
LAST_VALUE(col)	Last value in the window	Last order amount
NTH_VALUE(col, N)	N-th value in the window	The N-th order

▶ Example: LAG for Day-over-Day Change

SQL

SELECT
  order_date,
  daily_revenue,
  LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS prev_day,
  daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS diff,
  ROUND(
    (daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date))
    * 100.0 / NULLIF(LAG(daily_revenue, 1) OVER (ORDER BY order_date), 0),
  2) AS pct_change
FROM daily_revenue
ORDER BY order_date;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: LEAD to Look at Next Month

SQL

SELECT
  month,
  revenue,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_revenue;

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

(2) Notes on FIRST_VALUE / LAST_VALUE

LAST_VALUE's default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not the whole partition. You must specify the frame explicitly to get the partition's last row.

Function	Default frame	How to get the partition's last row
FIRST_VALUE	Up to CURRENT ROW (happens to be correct)	No change needed
LAST_VALUE	Up to CURRENT ROW (NOT the last row!)	Add `ROWS BETWEEN ... AND UNBOUNDED FOLLOWING`

▶ Example: FIRST_VALUE and LAST_VALUE

SQL

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

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

▶ Example: NTH_VALUE for the 2nd Order

SQL

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

TEXT

 id | name     | value 
----+----------+-------
  1 | example  | 42
(1 row)

8. Concept: Running Sum and Moving Average

(1) Running Sum

Frame	Meaning	SQL
Default frame	From partition start to current row	`SUM() OVER (ORDER BY col)`
Explicit ROWS	Same as above	`ROWS UNBOUNDED PRECEDING`
Explicit RANGE	Same-value rows grouped	`RANGE UNBOUNDED PRECEDING`

▶ Example: Monthly Cumulative Revenue

SQL

SELECT
  month,
  revenue,
  SUM(revenue) OVER (ORDER BY month) AS running_revenue
FROM monthly_revenue;

TEXT

  month   | revenue | running_revenue
----------+---------+----------------
 2025-01  |  500000 |         500000
 2025-02  |  620000 |        1120000
 2025-03  |  580000 |        1700000
 2025-04  |  710000 |        2410000

(2) Moving Average

▶ Example: 7-Day Moving Average

SQL

SELECT
  order_date,
  daily_revenue,
  ROUND(AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS ma_7day
FROM daily_revenue;

TEXT

  result  
----------
   42.50
(1 row)

(3) Partitioned Running Sum

▶ Example: Cumulative Spend per Customer

SQL

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS cumulative_spent
FROM orders
ORDER BY customer_id, order_date;

TEXT

  result  
----------
   42.50
(1 row)

9. Window Functions vs Aggregate Functions

Dimension	Aggregate function	Window function
Row count	Collapsed to one row	Original row count preserved
Syntax	`SUM(col)`	`SUM(col) OVER(...)`
GROUP BY	Required	Not needed
Compute per row	One dimension	Multiple different windows possible
Performance	Usually faster	Needs sort + partition, slightly slower
Best for	Summary reports	Ranking, offset, running totals

▶ Example: Equivalent Writing Styles Compared

Aggregate function style:

SQL

SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id;

TEXT

  result  
----------
   42.50
(1 row)

Window function style (keeps original rows):

SQL

SELECT
  order_id,
  customer_id,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

10. Comprehensive Example

Charlie's user retention analysis—counting daily new users and their Day 7 / Day 30 retention rates:

SQL

WITH first_login AS (
  SELECT
    user_id,
    MIN(login_date) AS first_date,
    ROW_NUMBER() OVER (PARTITION BY MIN(login_date) ORDER BY user_id) AS reg_rank
  FROM user_logins
  GROUP BY user_id
),
daily_new_users AS (
  SELECT
    first_date AS cohort_date,
    COUNT(*) AS new_users
  FROM first_login
  GROUP BY first_date
),
retention_base AS (
  SELECT
    fl.first_date AS cohort_date,
    fl.user_id,
    ul.login_date,
    ul.login_date - fl.first_date AS day_offset
  FROM first_login fl
  JOIN user_logins ul ON fl.user_id = ul.user_id
),
retention_count AS (
  SELECT
    cohort_date,
    day_offset,
    COUNT(DISTINCT user_id) AS retained_users
  FROM retention_base
  WHERE day_offset IN (0, 7, 30)
  GROUP BY cohort_date, day_offset
)
SELECT
  r.cohort_date,
  n.new_users,
  MAX(CASE WHEN r.day_offset = 0  THEN r.retained_users END) AS d0,
  MAX(CASE WHEN r.day_offset = 7  THEN r.retained_users END) AS d7,
  MAX(CASE WHEN r.day_offset = 30 THEN r.retained_users END) AS d30,
  ROUND(
    MAX(CASE WHEN r.day_offset = 7  THEN r.retained_users END) * 100.0
    / NULLIF(n.new_users, 0), 1
  ) AS day7_rate,
  ROUND(
    MAX(CASE WHEN r.day_offset = 30 THEN r.retained_users END) * 100.0
    / NULLIF(n.new_users, 0), 1
  ) AS day30_rate
FROM retention_count r
JOIN daily_new_users n ON r.cohort_date = n.cohort_date
GROUP BY r.cohort_date, n.new_users
ORDER BY r.cohort_date;

11. Execution Order

Where window functions sit in the SQL execution order:

flowchart TD
    A[FROM] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E["Window Functions<br/>OVER / PARTITION / ORDER / FRAME"]
    E --> F[SELECT]
    F --> G[DISTINCT]
    G --> H[ORDER BY]
    H --> I[LIMIT]

    style E fill:#c8e6c9
    style D fill:#fff9c4

Step	Clause	Description
1	FROM	Determine the data source
2	WHERE	Filter rows
3	GROUP BY	Aggregate grouping
4	HAVING	Filter groups
5	Window functions	Compute over the filtered result
6	SELECT	Choose output columns
7	DISTINCT	De-duplicate
8	ORDER BY	Final sorting
9	LIMIT	Limit rows

❓ FAQ

Q Can window functions appear in the WHERE clause?

A No. Window functions run after WHERE, so WHERE cannot reference their results. Wrap them in a subquery or CTE and filter there.

Q What's the difference between ROW_NUMBER and RANK?

A ROW_NUMBER is strictly increasing (1,2,3,4); RANK gives equal values the same rank and skips (1,1,3,4). For de-duplicated Top-1, use ROW_NUMBER.

Q Why doesn't LAST_VALUE return the partition's last row?

A The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. You must explicitly write ROWS BETWEEN ... AND UNBOUNDED FOLLOWING.

Q Can multiple window functions share one OVER clause?

A Yes. PostgreSQL supports the WINDOW clause alias: WINDOW w AS (PARTITION BY ...), then write OVER w in multiple functions.

Q What's the difference between ROWS and RANGE frames?

A ROWS offsets by physical rows; RANGE offsets by logical value. RANGE treats rows with the same ORDER BY value as one boundary. Most running-sum cases use ROWS.

Q How do I optimize window function performance?

A Make sure there are indexes on the PARTITION BY + ORDER BY columns; reduce the number of partitions; avoid complex frame calculations on large partitions; filter first with a CTE, then compute the window.

📖 Summary

Window functions don't collapse rows; each row returns a result, and the OVER clause defines the window
PARTITION BY partitions, ORDER BY orders, and the frame controls the calculation scope
ROW_NUMBER / RANK / DENSE_RANK behave differently; choose with care
LAG / LEAD access preceding/following rows; mind FIRST_VALUE / LAST_VALUE default frames
ROWS offsets by physical row, RANGE by logical value, GROUPS by grouped value
Running sums and moving averages are typical window-function use cases
Window functions run after WHERE/GROUP BY and cannot be used in WHERE
The WINDOW clause reuses OVER definitions, reducing duplication

📝 Exercises

⭐ Use ROW_NUMBER to query each customer's top 2 highest-amount orders.
⭐ Use LAG to compute the amount difference between adjacent orders for each customer.
⭐⭐ Compute the 7-day moving average of daily revenue per region.
⭐⭐ Use DENSE_RANK and NTILE(4) to split customers into 4 tiers by total spend.
⭐⭐⭐ Write a single SQL statement that computes the Day 1 / Day 7 / Day 30 retention rate of daily new users, using window functions instead of self-joins.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

404 Not Found

PostgreSQL Window Functions: A Detailed Guide

1. What You'll Learn

2. The Story

3. Concept: Window Function Basics

(1) What Is a Window Function

(2) The OVER Clause Structure

▶ Example: The Simplest Window Function

4. Concept: PARTITION BY and ORDER BY

(1) PARTITION BY for Partitioning

▶ Example: Summing Partitioned by Customer

(2) ORDER BY for Ordering

▶ Example: Ranking by Amount

(3) Combining PARTITION BY + ORDER BY

▶ Example: Order Amount Ranking per Customer

5. Concept: Frame Clause

(1) The Three Frame Types

(2) Frame Boundary Keywords

(3) Default Frame Rules

▶ Example: ROWS Frame Running Sum

▶ Example: 3-Row Moving Average with ROWS

▶ Example: RANGE Frame over a Date Interval

6. Concept: Ranking Functions

(1) The Four Ranking Functions Compared

▶ Example: ROW_NUMBER vs RANK vs DENSE_RANK

(2) NTILE Grouping

▶ Example: Splitting Customers into 4 Groups by Spend

7. Concept: Offset and Value Functions

(1) Offset Function Reference

▶ Example: LAG for Day-over-Day Change

▶ Example: LEAD to Look at Next Month

(2) Notes on FIRST_VALUE / LAST_VALUE

▶ Example: FIRST_VALUE and LAST_VALUE

▶ Example: NTH_VALUE for the 2nd Order

8. Concept: Running Sum and Moving Average

(1) Running Sum

▶ Example: Monthly Cumulative Revenue

(2) Moving Average

▶ Example: 7-Day Moving Average

(3) Partitioned Running Sum

▶ Example: Cumulative Spend per Customer

9. Window Functions vs Aggregate Functions

▶ Example: Equivalent Writing Styles Compared

10. Comprehensive Example

11. Execution Order

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好