Window Functions in PostgreSQL: The Real Beginner’s Guide
Window functions are one of those features that look scary at first glance, but once they “click,” you’ll wonder how you ever wrote reports or analytics without them!
They let you calculate things like running totals, rankings, and previous or next values while still seeing every row. That’s the key difference from GROUP BY: window functions add extra information without collapsing your result set.
In this guide, we’ll walk through window functions step by step using the Pagila Sample Database. By the end, you’ll be able to read and write real window function queries with confidence.
What Is a Window Function?
A window function is a function that performs a calculation across a set of rows that are related to the current row. PostgreSQL evaluates the function for each row, but it can “peek” at other rows in the same window.
The basic pattern looks like this:
function_name(...) OVER (
[PARTITION BY ...]
[ORDER BY ...]
[frame clause]
)
Think of OVER (...) as “define the group of rows and the order I care about.” The function then runs inside that window.
Common examples:
- SUM() OVER (...) — running totals or totals per group
- ROW_NUMBER() OVER (...) — row numbering
- RANK() OVER (...) — rankings with ties
- LAG()/LEAD() OVER (...) — previous or next row value
Let’s start simple and build up.
The Basic Pattern: SUM() OVER ()
Suppose we want to see each payment in Pagila’s payment table, plus the total amount each customer has paid, on every row.
With normal GROUP BY, you’d only get one row per customer. With a window function, you get both: the individual payment and the group total.
SELECT
customer_id,
payment_id,
amount,
SUM(amount) OVER () AS total_amount_all_customers
FROM payment
ORDER BY payment_id
LIMIT 10;
Here’s what’s happening:
- SUM(amount) is the aggregation.
- OVER () means “use all rows, no partitioning, no ordering.”
- Every row gets the same total: the sum of all payments in the table.
This isn’t very exciting yet, but it shows the idea: we’re aggregating without hiding rows.
PARTITION BY: Grouping Without Losing Rows
Now let’s do something actually useful: total payments per customer, but still keep each individual payment row.
SELECT
customer_id,
payment_id,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
) AS total_amount_per_customer
FROM payment
ORDER BY customer_id, payment_id
LIMIT 20;
Key idea: PARTITION BY customer_id splits the rows into mini-tables (one per customer). The window function runs separately in each partition.
Every row for the same customer gets the same total_amount_per_customer, but we still see each individual payment.
You can think of it as:
- Without window functions: one row per customer using GROUP BY.
- With window functions: all the rows plus the aggregated info.
ORDER BY in the Window: Adding Sequence
What if you want a running total per customer instead of just the final total? That’s where ORDER BY inside the window comes in.
SELECT
customer_id,
payment_id,
payment_date,
amount,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY payment_date, payment_id
) AS running_total_per_customer
FROM payment
ORDER BY customer_id, payment_date, payment_id
LIMIT 30;
Now PostgreSQL:
- Groups rows by customer_id (PARTITION BY).
- Orders payments within each customer by payment_date and payment_id.
- Computes a running sum of amount in that order.
The first payment for a customer gets their first amount. The second payment gets first + second. The third gets first + second + third. And so on.
This is a classic reporting pattern: “show me each row, plus the cumulative total up to that point.”
Window Frames: ROWS BETWEEN … AND …
Under the hood, PostgreSQL uses a frame to decide which rows belong in each calculation. By default, when you use ORDER BY with SUM() or AVG(), the frame is:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
In plain English: “from the first row in the partition up to the current row.” That’s why you get a running total.
You can customize the frame. For example, let’s compute a moving average of the last 3 payments per customer.
SELECT
customer_id,
payment_id,
payment_date,
amount,
AVG(amount) OVER (
PARTITION BY customer_id
ORDER BY payment_date, payment_id
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_last_3_payments
FROM payment
ORDER BY customer_id, payment_date, payment_id
LIMIT 30;
The frame ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means:
- For each row, look at the current row and the previous 2 rows (within the customer).
- Take the average of amount over that window.
Early rows (where fewer than 2 previous payments exist) just use whatever rows are available.
Ranking Rows: ROW_NUMBER, RANK, DENSE_RANK
Another super common use for window functions is ranking. Let’s rank customers by total amount spent.
First, use a subquery to compute total spent per customer:
SELECT
customer_id,
total_spent,
ROW_NUMBER() OVER (
ORDER BY total_spent DESC
) AS row_number_rank,
RANK() OVER (
ORDER BY total_spent DESC
) AS rank_with_gaps,
DENSE_RANK() OVER (
ORDER BY total_spent DESC
) AS dense_rank_no_gaps
FROM (
SELECT
customer_id,
SUM(amount) AS total_spent
FROM payment
GROUP BY customer_id
) AS customer_totals
ORDER BY total_spent DESC
LIMIT 20;
Differences:
- ROW_NUMBER() — strictly 1, 2, 3, 4… no matter what.
- RANK() — ties get the same rank, and the next rank is skipped.
- DENSE_RANK() — ties get the same rank, but no numbers are skipped.
This is perfect for leaderboards, top-N customers, or “top 10 per category” style queries.
Looking Backward and Forward: LAG and LEAD
Sometimes you want to compare a row to the previous or next row in a sequence. For example, how much did a customer’s payment change compared to their previous payment?
SELECT
customer_id,
payment_id,
payment_date,
amount,
LAG(amount) OVER (
PARTITION BY customer_id
ORDER BY payment_date, payment_id
) AS previous_amount,
amount - LAG(amount) OVER (
PARTITION BY customer_id
ORDER BY payment_date, payment_id
) AS difference_from_previous
FROM payment
ORDER BY customer_id, payment_date, payment_id
LIMIT 30;
Here:
- LAG(amount) grabs the amount from the previous row (within the same customer, ordered by date).
- The first payment per customer has no previous row, so previous_amount is NULL.
You can also use LEAD() to look ahead instead of behind.
Real-World Use Cases (That You’ll Actually Use)
Once you get comfortable with window functions, you’ll start seeing them everywhere. Some practical patterns:
- Running totals per customer, store, or product — revenue over time.
- Top N per group — top 3 customers per store, top 5 movies per category.
- Change detection — compare current value to previous one (LAG).
- Moving averages — smooth out noisy metrics.
- Sessionization — detect gaps between events using LAG() and timestamps.
The big win: you can express all of this in a single query, instead of juggling multiple temp tables or complex application code.
How Window Functions Differ from GROUP BY (Quick Mental Model)
If you only remember one thing from this article, make it this:
GROUP BY collapses rows. Window functions add columns.
- Use GROUP BY when you want one row per group (e.g., total sales per customer).
- Use window functions when you want to keep all the rows but also see aggregates, rankings, or comparisons.
You’ll often use both together: a GROUP BY in a subquery, and window functions in the outer query for ranking or further analysis.
Conclusion
Window functions look intimidating until you realize they all follow the same pattern: a function plus an OVER (...) clause that defines the window of rows to look at.
Start with simple examples like totals and running totals, then move into ranking and LAG()/LEAD(). Practice on the Pagila database with real tables like payment, customer, and rental, and these patterns will become second nature.
Once they click, window functions will become one of your favorite tools in PostgreSQL — and you’ll start seeing opportunities to use them everywhere!
