The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Count without groupingCOUNT(*) OVER () AS row_countView examples
Total each partitionSUM(amount) OVER (PARTITION BY department) AS department_totalView examples
Number rowsROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id ) AS positionView examples
Rank with gapsRANK() OVER (ORDER BY score DESC) AS rankView examples
Rank without gapsDENSE_RANK() OVER (ORDER BY score DESC) AS dense_rankView examples
Split into bucketsNTILE(4) OVER (ORDER BY score DESC) AS quartileView examples
Read the previous rowLAG(revenue) OVER (PARTITION BY product_id ORDER BY month ) AS previous_revenueView examples
Read the next rowLEAD(revenue) OVER (PARTITION BY product_id ORDER BY month ) AS next_revenueView examples
Read the first valueFIRST_VALUE(price) OVER (PARTITION BY sku ORDER BY changed_at ) AS initial_priceView examples
Read the partition's last valueLAST_VALUE(price) OVER (PARTITION BY sku ORDER BY changed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS final_priceView examples
Calculate a running totalSUM(amount) OVER ( ORDER BY occurred_at, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_totalView examples
Calculate a moving averageAVG(amount) OVER ( ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS three_row_averageView examples
Calculate percent of total100.0 * amount / NULLIF(SUM(amount) OVER (), 0) AS percent_totalView examples
Reuse a window definitionWINDOW by_account AS (PARTITION BY account_id ORDER BY occurred_at )View examples
Filter a window resultWITH ranked AS (...) SELECT * FROM ranked WHERE row_number = 1;View examples
Order the final resultSELECT ... ORDER BY department, rank, employee_id;View examples

Window functions calculate across related rows without collapsing them into one grouped result. Define the partition, ordering, and frame independently, keep the final result order explicit, and filter calculated window values in an outer query.

Step by step

Detailed examples

01

Preserve detail while calculating across rows

An ordinary aggregate with GROUP BY reduces each group to one row. The same aggregate used with OVER returns a value for every input row. PARTITION BY divides the input into independent windows; omitting it uses the whole filtered result. Window functions run after grouping and WHERE processing, so they operate on the rows that survive those stages.

Team detail with team and global totals
WITH sales (representative, team, amount) AS (
  VALUES ('Ada', 'East', 40), ('Grace', 'East', 60), ('Linus', 'West', 75)
)
SELECT representative, team, amount,
       SUM(amount) OVER (PARTITION BY team) AS team_total,
       COUNT(*) OVER () AS row_count
FROM sales
ORDER BY representative;
Output
representative | team | amount | team_total | row_count
---------------+------+--------+------------+----------
Ada            | East | 40     | 100        | 3
Grace          | East | 60     | 100        | 3
Linus          | West | 75     | 75         | 3
Back to quick reference ↑
02

Number and rank ordered rows

ROW_NUMBER always assigns distinct numbers, while RANK and DENSE_RANK treat rows equal under the window ORDER BY as peers. RANK leaves a gap after peer groups; DENSE_RANK does not. Add a stable tie-breaker to ROW_NUMBER whenever the chosen row must be repeatable. NTILE divides positions into a requested number of buckets rather than percentile boundaries.

Compare ranking behavior
WITH scores (id, team, score) AS (
  VALUES (1, 'A', 90), (2, 'A', 90), (3, 'A', 70), (4, 'A', 60)
)
SELECT id, score,
       ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) AS position,
       RANK() OVER (PARTITION BY team ORDER BY score DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY team ORDER BY score DESC) AS dense_rank,
       NTILE(2) OVER (PARTITION BY team ORDER BY score DESC, id) AS half
FROM scores
ORDER BY position;
Output
id | score | position | rank | dense_rank | half
---+-------+----------+------+------------+-----
1  | 90    | 1        | 1    | 1          | 1
2  | 90    | 2        | 1    | 1          | 1
3  | 70    | 3        | 3    | 2          | 2
4  | 60    | 4        | 4    | 3          | 2
Back to quick reference ↑
03

Compare values across ordered rows

LAG and LEAD access earlier or later rows in the same partition and accept optional offset and default arguments. FIRST_VALUE and LAST_VALUE operate on the window frame, not automatically the entire partition. With an ordered window, the default frame commonly ends at the current row's peer group, so LAST_VALUE usually needs an explicit UNBOUNDED FOLLOWING boundary.

Previous, next, first, and final prices
WITH prices (sku, changed_at, price) AS (
  VALUES ('A', DATE '2026-01-01', 10), ('A', DATE '2026-02-01', 12), ('A', DATE '2026-03-01', 11)
)
SELECT changed_at, price,
       LAG(price) OVER w AS previous_price,
       LEAD(price) OVER w AS next_price,
       FIRST_VALUE(price) OVER w AS initial_price,
       LAST_VALUE(price) OVER (
         PARTITION BY sku ORDER BY changed_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS final_price
FROM prices
WINDOW w AS (PARTITION BY sku ORDER BY changed_at)
ORDER BY changed_at;
Output
changed_at | price | previous_price | next_price | initial_price | final_price
-----------+-------+----------------+------------+---------------+------------
2026-01-01 | 10    | NULL           | 12         | 10            | 11
2026-02-01 | 12    | 10             | 11         | 10            | 11
2026-03-01 | 11    | 12             | NULL       | 10            | 11
Back to quick reference ↑
04

Make running and moving frames explicit

The frame selects rows around the current row after partitioning and ordering. ROWS counts physical rows; RANGE and GROUPS use ordering values or peer groups. Explicit ROWS boundaries make running totals and fixed-row moving averages readable and avoid surprises when duplicate ordering values are present. Include a unique tie-breaker when row sequence matters.

Running total and three-row average
WITH daily (id, day, amount) AS (
  VALUES (1, DATE '2026-01-01', 10), (2, DATE '2026-01-02', 20),
         (3, DATE '2026-01-03', 30), (4, DATE '2026-01-04', 40)
)
SELECT day, amount,
       SUM(amount) OVER (ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
       AVG(amount) OVER (ORDER BY day, id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average
FROM daily
ORDER BY day, id;
Output
day        | amount | running_total | moving_average
-----------+--------+---------------+---------------
2026-01-01 | 10     | 10            | 10.0000
2026-01-02 | 20     | 30            | 15.0000
2026-01-03 | 30     | 60            | 20.0000
2026-01-04 | 40     | 100           | 30.0000
Back to quick reference ↑
05

Calculate shares without losing rows

A window aggregate can be used inside ordinary arithmetic, which makes ratios to partition or global totals concise. Promote integer arithmetic with a decimal literal when fractional output matters. NULLIF protects division when the denominator can be zero, and the resulting null honestly represents an undefined percentage.

Share of all revenue
WITH revenue (product, amount) AS (VALUES ('A', 25), ('B', 75))
SELECT product, amount,
       ROUND(100.0 * amount / NULLIF(SUM(amount) OVER (), 0), 1) AS percent_total
FROM revenue
ORDER BY product;
Output
product | amount | percent_total
--------+--------+--------------
A       | 25     | 25.0
B       | 75     | 75.0
Back to quick reference ↑
06

Reuse a shared window specification

A WINDOW clause names a definition after HAVING and before the final ORDER BY. Several functions can reuse it, reducing repetition and keeping partition and ordering rules aligned. A use site can extend a compatible named window, but it cannot override clauses that the referenced definition already supplies.

Several account calculations from one window
WITH transactions (account_id, occurred_at, amount) AS (
  VALUES (1, DATE '2026-01-01', 10), (1, DATE '2026-01-02', 15)
)
SELECT account_id, occurred_at, amount,
       LAG(amount) OVER by_account AS previous_amount,
       SUM(amount) OVER (by_account ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance
FROM transactions
WINDOW by_account AS (PARTITION BY account_id ORDER BY occurred_at)
ORDER BY account_id, occurred_at;
Output
account_id | occurred_at | amount | previous_amount | balance
-----------+-------------+--------+-----------------+--------
1          | 2026-01-01  | 10     | NULL            | 10
1          | 2026-01-02  | 15     | 10              | 25
Back to quick reference ↑
07

Filter calculated windows in an outer query

WHERE is evaluated before window functions, so a window expression cannot be filtered at the same query level. Compute it in a subquery or CTE, then filter the named result outside. Ordering inside OVER determines the calculation but does not promise presentation order; use a final ORDER BY for deterministic output.

Highest-scoring employee per department
WITH employees (employee_id, department, score) AS (
  VALUES (1, 'Docs', 90), (2, 'Docs', 95), (3, 'Ops', 88)
), ranked AS (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY department ORDER BY score DESC, employee_id
  ) AS row_number
  FROM employees
)
SELECT employee_id, department, score
FROM ranked
WHERE row_number = 1
ORDER BY department, employee_id;
Output
employee_id | department | score
------------+------------+------
2           | Docs       | 95
3           | Ops        | 88
Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupPostgreSQL: Window Functionspostgresql.org
  2. PostgreSQL Global Development GroupPostgreSQL Tutorial: Window Functionspostgresql.org
  3. PostgreSQL Global Development GroupPostgreSQL: Window Function Callspostgresql.org
  4. PostgreSQL Global Development GroupPostgreSQL: SELECTpostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback