Many SQL problems ask for a calculation across several rows while still returning each original row.
For example, you may need to show every order together with the customer’s running spend, rank products inside each category, or compare today’s measurement with the previous one. A regular aggregate such as SUM() or AVG() can calculate across rows, but a GROUP BY query usually collapses those rows into one result row per group.
A window function solves a different problem:
GROUP BY -> combines rows into fewer result rows
window function -> calculates across related rows without collapsing themThat distinction is the core mental model. Window functions let a row remain visible while borrowing context from other rows.
This article uses syntax supported by PostgreSQL and SQLite for the examples. Other SQL databases support many of the same window-function concepts, but exact functions and frame options can differ.
Start with the problem GROUP BY cannot solve directly
Suppose an orders table contains:
order_id | customer_id | ordered_at | total
---------+-------------+-------------+------
101 | A | 2026-01-03 | 40
102 | A | 2026-01-08 | 25
103 | B | 2026-01-09 | 60
104 | A | 2026-01-12 | 15A grouped query can calculate one total per customer:
SELECT customer_id, SUM(total) AS customer_total
FROM orders
GROUP BY customer_id;That is useful when one row per customer is exactly what you want.
But it cannot also return every order_id from the original table without changing the query shape. The grouped rows no longer represent individual orders.
A window aggregate keeps those detail rows:
SELECT
order_id,
customer_id,
total,
SUM(total) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;Each order remains in the result. SUM(total) is evaluated across the rows in the same customer partition, then its result is attached to each relevant order row.
Understand the three parts of a window
The general shape is:
function(...) OVER (
PARTITION BY ...
ORDER BY ...
ROWS BETWEEN ...
)Not every window needs every clause, but the clauses answer three different questions.
PARTITION BY chooses the group of related rows
PARTITION BY divides the input rows into independent partitions.
SUM(total) OVER (
PARTITION BY customer_id
)Each customer’s orders are calculated separately.
A partition is similar to a GROUP BY group in how it defines related rows, but it does not collapse those rows.
If you omit PARTITION BY, the entire input to the window function is one partition.
ORDER BY defines sequence inside each partition
Some calculations depend on row order:
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at
)The ORDER BY inside OVER determines the ordering used by the window function.
It is separate from the query’s final ORDER BY. This means a window can calculate in one order while the result is displayed in another.
If the window calculation requires deterministic ordering, include enough columns to break ties. For example:
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
)If two rows have the same ordered_at value and no additional tie-breaker, their relative row numbers are not guaranteed by that ordering alone.
The frame chooses which nearby rows participate
For aggregate window functions, a window frame narrows the current partition to rows relative to the current row.
A common explicit frame is:
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWIt means:
first row in partition
|
v
[ row ][ row ][ current row ]
^
|
frame ends hereThis is useful for running aggregates.
The difference between the partition and the frame is important:
partition -> all related rows available to the window
frame -> subset used for this current row's aggregate calculationBuild a running total with an explicit frame
A running total should grow as later rows are processed.
SELECT
order_id,
customer_id,
ordered_at,
total,
SUM(total) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders
ORDER BY customer_id, ordered_at, order_id;For customer A, the result is conceptually:
order_id | total | running_total
---------+-------+--------------
101 | 40 | 40
102 | 25 | 65
104 | 15 | 80The partition resets when customer_id changes. Inside each partition, the rows are ordered by ordered_at, order_id. The ROWS frame then includes every row from the beginning of that partition through the current row.
Why write the ROWS frame explicitly?
When an aggregate window function has ORDER BY but no explicit frame, the database applies a default frame. In PostgreSQL and SQLite, that default is based on RANGE ... CURRENT ROW, which includes peer rows that tie on the window ordering values.
That can be surprising when multiple rows have the same ordering value.
Suppose two payments share the same timestamp:
payment_id | paid_at | amount
-----------+---------+-------
1 | 10:00 | 20
2 | 10:00 | 30With a default peer-aware frame, both rows can see the same cumulative aggregate for the tied ordering value.
If you mean “include the rows physically preceding this row in the window ordering,” use an explicit ROWS frame and provide a deterministic tie-breaker:
SUM(amount) OVER (
ORDER BY paid_at, payment_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)Being explicit makes the intended boundary behavior easier to review.
Rank rows within each group
ROW_NUMBER(), RANK(), and DENSE_RANK() all assign positions, but ties are handled differently.
Consider product scores:
category | product | score
---------+---------+------
books | A | 95
books | B | 95
books | C | 90The query:
SELECT
category,
product,
score,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY score DESC
) AS row_number,
RANK() OVER (
PARTITION BY category
ORDER BY score DESC
) AS rank,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY score DESC
) AS dense_rank
FROM product_scores;produces these ranking patterns:
score | row_number | rank | dense_rank
------+------------+------+-----------
95 | 1 or 2 | 1 | 1
95 | 2 or 1 | 1 | 1
90 | 3 | 3 | 2ROW_NUMBER() always assigns a distinct sequence number. If the ordering does not distinguish tied rows, their relative numbers are not deterministic.
RANK() gives peers the same rank and leaves a gap after the tie.
DENSE_RANK() also gives peers the same rank but does not leave that gap.
Choose based on the meaning you need, not on which output looks nicest.
Solve top-N-per-group with ROW_NUMBER
A common requirement is “return the two most recent orders for every customer.”
Trying to solve that with a global LIMIT 2 is wrong because it limits the whole result, not each customer.
First rank rows inside each customer partition:
SELECT
order_id,
customer_id,
ordered_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at DESC, order_id DESC
) AS position
FROM orders;Then filter the ranked result in an outer query:
SELECT order_id, customer_id, ordered_at
FROM (
SELECT
order_id,
customer_id,
ordered_at,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at DESC, order_id DESC
) AS position
FROM orders
) AS ranked_orders
WHERE position <= 2
ORDER BY customer_id, position;The outer query is necessary in portable SQL because window functions are evaluated after WHERE. A WHERE clause in the same query level cannot generally filter on a window result that has not been computed yet.
Some databases offer additional syntax such as QUALIFY, but it is not portable across all systems, so the subquery pattern remains broadly useful.
Compare a row with the previous row using LAG
Window functions are also useful when the problem is not aggregation.
LAG() returns a value from an earlier row in the current partition according to the window ordering.
Suppose a table stores daily account balances:
SELECT
account_id,
measured_on,
balance,
LAG(balance) OVER (
PARTITION BY account_id
ORDER BY measured_on
) AS previous_balance
FROM daily_balances;The first row in each account partition has no previous row, so LAG() returns NULL unless a default argument is provided.
You can calculate a day-to-day change:
SELECT
account_id,
measured_on,
balance,
balance - LAG(balance) OVER (
PARTITION BY account_id
ORDER BY measured_on
) AS change_from_previous
FROM daily_balances;For the first row, the subtraction also produces NULL because there is no previous balance.
That is often the correct representation: the change is unknown because there is no earlier row to compare.
If your application wants a different business meaning, make it explicit rather than automatically replacing the missing comparison with zero.
LEAD looks forward instead of backward
LEAD() is the corresponding forward-looking function.
For example, you can show the next scheduled event for each device:
SELECT
device_id,
event_at,
LEAD(event_at) OVER (
PARTITION BY device_id
ORDER BY event_at
) AS next_event_at
FROM device_events;This avoids a self-join whose only purpose is to locate the adjacent row in an ordered sequence.
The advantage is clarity when “previous” or “next” is genuinely the problem being expressed.
It is not automatically faster than every join-based alternative. Query plans depend on indexes, row counts, sort requirements, database implementation, and the surrounding query.
Window ORDER BY does not sort the final result
This query numbers rows by descending score:
SELECT
product,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS position
FROM product_scores;But the SQL result is not guaranteed to be returned in score DESC order merely because the window uses that ordering.
If presentation order matters, add an outer query-level order:
SELECT
product,
score,
ROW_NUMBER() OVER (ORDER BY score DESC) AS position
FROM product_scores
ORDER BY score DESC, product;Think of the two clauses separately:
ORDER BY inside OVER -> order used for the calculation
final ORDER BY -> order used for returned rowsConflating them can produce code that appears correct during testing but relies on an ordering the query never requested.
Understand peers before relying on ranking or frames
Rows with equal values according to a window’s ORDER BY expressions are often called peers.
For example:
RANK() OVER (ORDER BY score DESC)treats rows with the same score as peers.
This is exactly what RANK() and DENSE_RANK() need.
But peer behavior also matters for aggregate frames. A default RANGE ... CURRENT ROW frame includes the current row’s peers. That is why tied ordering values can make a running aggregate advance in groups rather than one row at a time.
If row-by-row progression matters, use:
- an ordering that deterministically distinguishes rows, and
- an explicit
ROWSframe.
If tied values should deliberately behave as a unit, peer-aware behavior may be exactly what you want.
Use the whole partition when you want a group total
An aggregate window does not need ORDER BY when the calculation should use the entire partition.
For example:
SELECT
order_id,
customer_id,
total,
SUM(total) OVER (
PARTITION BY customer_id
) AS customer_total
FROM orders;Without a window ORDER BY, the aggregate sees the whole partition rather than a growing sequence.
You can then calculate each order’s share of its customer’s total:
SELECT
order_id,
customer_id,
total,
total * 1.0
/ SUM(total) OVER (PARTITION BY customer_id)
AS share_of_customer_total
FROM orders;The multiplication by 1.0 is shown to encourage non-integer arithmetic in systems where the operand types could otherwise lead to integer division. Exact numeric coercion rules vary by database and column type, so use an explicit cast when your database’s type rules require one.
Also consider the zero-total case. Dividing by a partition total of zero may raise an error or produce a database-specific result. If zero is valid data, handle that condition explicitly.
Multiple window functions can share the same partition
A query can calculate several window values from the same rows:
SELECT
customer_id,
order_id,
ordered_at,
total,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
) AS order_number,
SUM(total) OVER (
PARTITION BY customer_id
ORDER BY ordered_at, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;The numbering and running total use the same logical ordering but perform different calculations.
Some databases support naming a shared window definition with a WINDOW clause. That can reduce repetition when many expressions use the same partition and ordering. Before using it in portable application SQL, verify support in every target database.
Filtering happens before window calculation
Window functions operate on the row set that survives earlier query processing such as FROM, WHERE, GROUP BY, and HAVING.
This means the location of a filter changes the data visible to the window.
Consider:
SELECT
order_id,
customer_id,
total,
SUM(total) OVER (
PARTITION BY customer_id
) AS visible_customer_total
FROM orders
WHERE total >= 50;The window total includes only orders that passed WHERE total >= 50.
It does not calculate the total across every order and then hide the smaller orders afterward.
If you need “calculate over all orders, then filter displayed rows,” put the calculation in an inner query and filter in an outer one:
SELECT order_id, customer_id, total, customer_total
FROM (
SELECT
order_id,
customer_id,
total,
SUM(total) OVER (
PARTITION BY customer_id
) AS customer_total
FROM orders
) AS calculated_orders
WHERE total >= 50;This query shape makes the intended data boundary explicit.
Window functions often require sorting work
Window functions are expressive, but they are not free.
A window with:
PARTITION BY customer_id
ORDER BY ordered_atrequires the database to process rows in an order compatible with that partitioning and ordering. Depending on the query plan and available indexes, that can require a sort or other substantial work.
An index whose leading columns match useful filtering and ordering patterns may help some workloads, but there is no universal “index for window functions” rule. The optimizer must consider the whole query, not just the OVER clause.
For large tables:
- inspect the actual query plan;
- measure with realistic row counts;
- avoid computing windows over far more rows than the result requires when an earlier, semantically correct filter can reduce the input;
- do not add indexes solely from syntax without checking whether the database uses them.
Performance advice should follow the execution plan and workload, not the presence of a window function alone.
Common mistakes
Using GROUP BY when detail rows must remain visible
GROUP BY is appropriate when the desired result really has one row per group. If the original rows must remain, a window aggregate is often the more direct model.
Forgetting a tie-breaker for ROW_NUMBER
If the window ordering contains ties, ROW_NUMBER() can assign those peer rows in an unspecified relative order.
Add a stable unique column when deterministic numbering matters.
Assuming the default frame means one row at a time
With an ordered aggregate window, default peer-aware frame behavior can make tied rows share the same cumulative result.
Use an explicit ROWS frame for row-by-row accumulation.
Filtering too early
A WHERE clause removes rows before window functions see them.
If the calculation must include rows that should not appear in the final output, calculate in a subquery first and filter outside.
Assuming window ordering controls display ordering
Only a query-level ORDER BY requests final result ordering.
Using a window function where a simple aggregate is clearer
If you only need one total per customer, this is clearer:
SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id;A window function adds value when detail rows or row-relative calculations must remain available.
When window functions are the right tool
Window functions are a strong fit when the result needs both row detail and context from related rows.
Typical examples include:
- rankings within categories;
- top-N rows per group;
- running totals;
- moving calculations;
- previous/next-row comparisons;
- per-row percentages of a group total;
- sequence numbering without collapsing rows.
They are less useful when the desired output naturally has one row per group, when a straightforward join expresses the relationship more clearly, or when database compatibility rules out the required window feature.
Keep rows and context separate in your mental model
The most useful way to understand a window function is that it adds context to a row without changing that row into a group result.
PARTITION BY chooses the related rows. ORDER BY defines their sequence. The frame controls which part of that sequence participates in aggregate calculations. Functions such as ROW_NUMBER(), RANK(), SUM(), LAG(), and LEAD() then answer different questions over that context.
Once you keep those responsibilities separate, window queries become easier to design and review. Use GROUP BY when rows should collapse. Use a window when the rows should stay visible while the query calculates across them.