Need a running total, a ranking, or a month-over-month comparison in SQL? The usual instinct is a self-join, a correlated subquery, or a stack of CTEs. PostgreSQL window functions make those patterns shorter, faster, and far easier to read — without collapsing your rows the way GROUP BY does. This step-by-step tutorial walks through window functions on a real sales table, from running totals to rankings and moving averages.

If you are building dashboards for a Houston energy firm, shipment reports for a Gulf Coast logistics operation, or revenue tracking for a SaaS product, these are the patterns you will reach for weekly. The PostgreSQL tutorial on window functions is a great first read; this guide gives you copy-paste examples you can adapt today.

What Makes a Window Function a “Window”?

A window function computes a value across a set of rows that are related to the current row, then returns that value next to the row itself. The three clauses inside OVER() define the window:

• PARTITION BY — splits rows into independent groups, like one window per region.
• ORDER BY — sets the sequence of rows inside each group.
• A frame clause — narrows the calculation to a sliding range of rows (for example, “the last 3 rows”).

The key difference from an aggregate: a window function never merges rows. Every input row survives, which makes window functions ideal for reports that need both detail and summary in one result set.

Before You Start: Create the Sample Data

Run this in psql or the pgAdmin query tool to create a small quarterly sales table with two regions:

CREATE TABLE sales (
  sale_id   serial PRIMARY KEY,
  region    text NOT NULL,
  sale_date date NOT NULL,
  amount    numeric(10,2) NOT NULL
);

INSERT INTO sales (region, sale_date, amount) VALUES
  ('South', '2026-01-15', 1250.00),
  ('South', '2026-02-14',  980.50),
  ('South', '2026-03-20', 1740.25),
  ('South', '2026-04-18', 1510.00),
  ('North', '2026-01-22',  900.00),
  ('North', '2026-02-10', 2100.75),
  ('North', '2026-03-05', 1325.00),
  ('North', '2026-04-25', 1890.40);

Step 1: Compute a Running Total with SUM() OVER()

A running total is the classic window-function use case. PARTITION BY region resets the total for each region, and ORDER BY sale_date tells PostgreSQL to add rows in chronological order:

SELECT region,
       sale_date,
       amount,
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales
ORDER BY region, sale_date;

The South region now shows 1250.00, then 2230.50, then 3970.75, then 5480.75 — a cumulative total that grows row by row. Notice the query still returns all eight rows; nothing was aggregated away. Swap the aggregate to COUNT() and the same shape gives you a running row count; swap to AVG() and it gives a cumulative average.

Step 2: Rank Rows with ROW_NUMBER(), RANK(), and DENSE_RANK()

Ranking functions answer “where does this row fall inside its group?” — for example, the largest sale per region:

SELECT region,
       sale_date,
       amount,
       ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_num,
       RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rank,
       DENSE_RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS dense_rank
FROM sales
ORDER BY region, row_num;

All three functions assign 1 to the biggest sale in each region, but they differ on ties: ROW_NUMBER() breaks ties arbitrarily and never repeats a number; RANK() leaves gaps after ties (1, 1, 3); DENSE_RANK() does not (1, 1, 2). For a “top 3 sales per region” report, use RANK() or DENSE_RANK() depending on whether you want to include all tied rows.

One trap: WHERE runs before window functions, so you cannot filter on row_num <= 3 in the same query. Wrap the query in a subquery or CTE and filter in the outer SELECT.

Step 3: Compare Rows with LAG() and Moving Averages

To see the previous sale amount for each region, use LAG(). Add a frame clause to AVG() and you get a three-row moving average — handy for smoothing noisy metrics like daily well production or web traffic:

SELECT region,
       sale_date,
       amount,
       LAG(amount) OVER (PARTITION BY region ORDER BY sale_date) AS prev_amount,
       AVG(amount) OVER (PARTITION BY region ORDER BY sale_date
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3
FROM sales
ORDER BY region, sale_date;

LAG(amount) pulls the value from the row immediately before the current one inside the same region, which makes month-over-month change trivial: subtract prev_amount from amount. The frame clause ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the average to the current row and the two rows before it, so the average slides forward as the window moves. The counterpart to LAG() is LEAD(), which looks forward instead of back.

Window Functions vs. GROUP BY

Reach for GROUP BY when you want one summary row per group. Reach for a window function when you want the summary attached to every detail row — rankings, running totals, “share of total” percentages, and row-over-row deltas. When you need both (a total per region and each row in that region), a window function does it in one pass instead of a join back to an aggregate.

Three Pitfalls to Avoid

• Forgetting ORDER BY changes the frame. With ORDER BY present and no explicit frame, PostgreSQL defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which is why the running total works. Without ORDER BY, the whole partition is the frame.
• Filtering window results in WHERE. Window functions run after WHERE and GROUP BY, so rank or row-number filters must live in an outer query.
• Ignoring index support. Each PARTITION BY ... ORDER BY ... pair may trigger a sort. An index on (region, sale_date) lets PostgreSQL read rows already in order, and EXPLAIN will confirm whether the sort disappears.

Next Steps

Once the basics feel natural, read the official window function reference for the full list — NTILE(), FIRST_VALUE(), NTH_VALUE(), and the percentile functions are all there. Then practice on your own data: pick a table you query often and rewrite one self-join or subquery as a window function. The result will usually be shorter, and it will usually teach you something about how PostgreSQL orders and partitions data.