PostgreSQL window functions: ranking, totals, and frames

PostgreSQL window functions add calculations such as a rank, running total, or previous-row value while keeping individual result rows. The OVER clause defines the window. Before writing it, decide which rows belong together, how they should be ordered, and which rows each calculation should include.

This guide uses six sales records to make those choices visible. We ran 28 checks on an isolated PostgreSQL 18.6 server, including every result shown below. The examples test query behavior, not production speed.

Start with a small sales table

Use a disposable database where window_lab is unused. Team A has two sales on the same day with the same amount. There is no sale on January 2. Those details will expose ties and the difference between counting rows and counting days.

CREATE SCHEMA window_lab;
CREATE TABLE window_lab.sales (
    id integer PRIMARY KEY,
    team text NOT NULL,
    sold_on date NOT NULL,
    amount integer NOT NULL
);
INSERT INTO window_lab.sales VALUES
(1, 'A', '2026-01-01', 100),
(2, 'A', '2026-01-01', 100),
(3, 'A', '2026-01-03', 50),
(4, 'A', '2026-01-04', 20),
(5, 'B', '2026-01-01', 80),
(6, 'B', '2026-01-03', 30);

The six amounts total 380: 270 for A and 110 for B. Amounts are plain integer units, so the examples do not introduce currency rounding.

Keep each sale while adding partition totals

PARTITION BY team separates the calculation by team. Empty parentheses in OVER () use all input rows together. Neither sum below has a window ordering, so each covers its whole partition.

SELECT id, team, amount,
       sum(amount) OVER (PARTITION BY team) AS team_total,
       sum(amount) OVER () AS all_total
FROM window_lab.sales
ORDER BY team, id;

Captured output follows the selected column order: ID, team, amount, team total, and overall total.

1|A|100|270|380
2|A|100|270|380
3|A|50|270|380
4|A|20|270|380
5|B|80|110|380
6|B|30|110|380

We still have six result rows. A separate GROUP BY team check returned just two rows, A with 270 and B with 110. Choose the grouped form when you only need one row per team; use the window form when each sale needs its team context. PostgreSQL’s window-function tutorial introduces this distinction.

Choose a ranking rule and handle ties deliberately

These three columns answer different questions. The first assigns each sale a unique position. The other two rank amounts and preserve equal amounts as ties.

SELECT id, team, amount,
       row_number() OVER (PARTITION BY team ORDER BY amount DESC, id) AS position,
       rank() OVER (PARTITION BY team ORDER BY amount DESC) AS tied_rank,
       dense_rank() OVER (PARTITION BY team ORDER BY amount DESC) AS dense_position
FROM window_lab.sales
ORDER BY team, amount DESC, id;

Columns after amount are position, tied_rank, and dense_position:

1|A|100|1|1|1
2|A|100|2|1|1
3|A|50|3|3|2
4|A|20|4|4|3
5|B|80|1|1|1
6|B|30|2|2|2

For team A, row numbers are 1, 2, 3, 4. The two amounts of 100 share rank 1, so the next rank is 3. Dense ranks are 1, 1, 2, 3, with no gap after the tie.

Only row_number() uses ID as a tie-breaker here. Adding ID to the other two window orderings would make the two sales distinct for ranking, defeating the intended amount-based tie. We reinserted the fixture in reverse order and confirmed that the explicit row-number ordering still returned the same positions.

The final ORDER BY controls presentation. It does not repair an incomplete ordering inside OVER. The window-function reference defines ranks, peers, and neighboring-row functions.

Make the running-total frame explicit

The next query compares three calculations: a total through the current date, a total through the current sale, and a total over at most two consecutive sales.

SELECT id, team, sold_on, amount,
       sum(amount) OVER (PARTITION BY team ORDER BY sold_on) AS through_day,
       sum(amount) OVER (
           PARTITION BY team ORDER BY sold_on, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS through_row,
       sum(amount) OVER (
           PARTITION BY team ORDER BY sold_on, id
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS two_rows
FROM window_lab.sales
ORDER BY team, sold_on, id;

The last three columns are through_day, through_row, and two_rows:

1|A|2026-01-01|100|200|100|100
2|A|2026-01-01|100|200|200|200
3|A|2026-01-03|50|250|250|150
4|A|2026-01-04|20|270|270|70
5|B|2026-01-01|80|80|80|80
6|B|2026-01-03|30|110|110|110

The first two A rows both show 200 in through_day. Ordering only by date makes them peers, and the default frame includes both. In through_row, ID breaks the tie and the explicit ROWS frame advances one sale at a time: 100, 200, 250, 270.

Two choices changed between those columns: the ordering and the frame. To separate their effects, we also added ID to the first column’s window ordering. With no peers remaining, that default-frame total matched the explicit ROWS total. Specifying ROWS without a complete ordering would still leave the sequence of tied rows unspecified.

For A’s January 3 sale, two_rows is 150: the current 50 plus the preceding sale of 100 on January 1. It is a two-row total, not a two-day total. A missing date does not create a zero-valued row. Choose a calendar-based calculation separately if that is the reporting requirement.

The frame syntax reference distinguishes ROWS, RANGE, and GROUPS. Our examples exercise row frames and the default peer-aware prefix; they do not test every frame option.

Compare each sale with the previous sale using LAG

A named window keeps the ordering identical for both expressions:

SELECT id, team, sold_on, amount,
       lag(amount) OVER w AS previous_amount,
       amount - lag(amount) OVER w AS change
FROM window_lab.sales
WINDOW w AS (PARTITION BY team ORDER BY sold_on, id)
ORDER BY team, sold_on, id;

The last two columns are the previous amount and the difference. Empty fields in this psql output represent SQL NULL.

1|A|2026-01-01|100||
2|A|2026-01-01|100|100|0
3|A|2026-01-03|50|100|-50
4|A|2026-01-04|20|50|-30
5|B|2026-01-01|80||
6|B|2026-01-03|30|80|-50

The first sale in each team has no predecessor. A’s January 3 sale compares with the preceding recorded sale on January 1, producing −50. This is not a comparison with the previous calendar day.

We also checked a separate sequence containing a NULL amount. An explicit third argument to lag supplied a default only when there was no preceding row; it did not replace an existing preceding NULL. Narrowing the frame to the current row did not change our LAG results. The function looks at an offset within the partition rather than a row selected by that frame.

Check what LAST_VALUE considers last

Here both columns use the same unique ordering, so the frame is the only difference:

SELECT id, team, amount,
       last_value(amount) OVER (
           PARTITION BY team ORDER BY sold_on, id
       ) AS frame_last,
       last_value(amount) OVER (
           PARTITION BY team ORDER BY sold_on, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS team_last
FROM window_lab.sales
ORDER BY team, sold_on, id;

The final columns are frame_last and team_last:

1|A|100|100|20
2|A|100|100|20
3|A|50|50|20
4|A|20|20|20
5|B|80|80|30
6|B|30|30|30

With the default frame, the last row is the current row in this ordering. That makes frame_last equal the current amount. Extending the frame through UNBOUNDED FOLLOWING gives the final sale amount for the team: 20 for every A row and 30 for every B row. “Last” means last in the chosen order, not largest amount.

Filter rankings in an outer query

To keep two sales per team, calculate the row number inside a subquery and filter the result outside:

SELECT id, team, amount, position
FROM (
    SELECT id, team, amount,
           row_number() OVER (
               PARTITION BY team ORDER BY amount DESC, id
           ) AS position
    FROM window_lab.sales
) AS ranked
WHERE position <= 2
ORDER BY team, position;

1|A|100|1
2|A|100|2
5|B|80|1
6|B|30|2

This selects at most two rows per team, with ID resolving equal amounts. It does not promise to include every tie at the cutoff. If the requirement is the top two distinct amounts, dense_rank() ordered only by amount expresses a different rule. Our variation returned three A rows and two B rows.

A same-level WHERE row_number() OVER (...) <= 2 failed in our test with SQLSTATE 42P20. An ordinary filter before the window also changes the input: keeping only amounts of at least 50 reduced the team totals to 250 and 80. Choose whether the report needs totals before filtering or totals over only the retained sales. The SELECT reference describes the relevant query stages.

Validate results before investigating performance

Our checks compared exact results, repeated ordering tests after reversing insertion order, and independently accumulated the running totals in Python. Empty input and a one-row partition were also checked. These tests establish behavior for the fixture; they do not predict the cost of sorting millions of rows.

For a real workload, inspect the query with the PostgreSQL EXPLAIN ANALYZE guide. Remember that EXPLAIN ANALYZE runs the query. The pg_stat_statements guide can help identify which queries merit that investigation. Neither a window function nor an extra index guarantees a faster query.

When finished, remove only the objects created for these examples:

DROP TABLE window_lab.sales;
DROP SCHEMA window_lab;

The lab cleanup passed, and the isolated server was stopped after verification. For your own query, first write down the partition, tie rule, ordering, and frame. Then check a tie, a boundary row, and a missing date against the result you actually want.