SQL Window Functions Explained (With Examples)

A window function computes a value across a set of related rows — a “window” — without collapsing those rows into one, the way GROUP BY does. Each row keeps its own identity in the result, but gains a calculated column that’s aware of its neighbors: a running total, a rank, a comparison to the previous row.

The problem with GROUP BY

GROUP BY is great when you want one output row per group — total revenue per region, count of orders per customer. But it can’t answer “show me every order, along with what percentage of that customer’s total spend this order represents.” That question needs per-row detail and group-level context at the same time, and GROUP BY forces you to pick one.

Window functions give you both. The syntax adds an OVER clause to a function call:

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Every row still appears individually, but customer_total shows the sum across that customer’s entire set of orders — computed without a GROUP BY and without a self-join.

Anatomy of OVER

The OVER clause defines the window for each row. Its three parts each answer a different question:

• PARTITION BY — which rows belong together? This splits the result set into groups, similar to GROUP BY, but without merging rows.
• ORDER BY (inside OVER) — what order do rows within a partition come in? Required for functions like running totals and rankings, where sequence matters.
• Frame clause (ROWS BETWEEN ... AND ...) — which rows within the ordered partition actually contribute to this row’s calculation? Defaults to everything from the start of the partition through the current row when ORDER BY is present.

SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

That query gives each order a running total of everything that customer has spent up to and including that order — a classic running-balance pattern.

Ranking functions

Beyond aggregates like SUM and AVG, several functions exist purely for window use:

Function	What it does
ROW_NUMBER()	Sequential number per row within the partition, no ties
RANK()	Rank with gaps after ties (1, 2, 2, 4)
DENSE_RANK()	Rank without gaps after ties (1, 2, 2, 3)
LAG(col, n)	Value from n rows before the current row
LEAD(col, n)	Value from n rows after the current row
NTILE(n)	Splits the partition into n roughly equal buckets

ROW_NUMBER() is the workhorse for “top N per group” queries, which are otherwise awkward in plain SQL:

SELECT * FROM (
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

This returns the three highest-paid employees per department — a query that would otherwise require a correlated subquery or a self-join with a count.

LAG and LEAD are the natural fit for period-over-period comparisons:

SELECT
  order_date,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY order_date) AS change_from_prior_day
FROM daily_revenue;

No self-join, no subquery — just direct access to the adjacent row.

Window functions vs GROUP BY

	GROUP BY	Window functions
Output rows	One per group	One per input row
Row-level detail	Lost after aggregation	Preserved
Combining detail + aggregate	Requires a self-join or subquery	Native
Ranking within groups	Awkward	Built-in (RANK, ROW_NUMBER)
Running totals	Not directly supported	Built-in with frame clauses

They’re not competitors so much as tools for different questions — you’ll often see both in the same query, with a GROUP BY aggregation feeding into a query that then window-functions over the grouped result.

Where they fit in query design

Window functions run after WHERE, GROUP BY, and HAVING are evaluated, but before ORDER BY and LIMIT. That ordering is why you generally can’t filter directly on a window function’s result in the same SELECT — WHERE rn <= 3 fails because WHERE runs before rn exists. That’s the reason the “top N per group” pattern above wraps the window function in a subquery and filters in the outer query instead.

They also interact with how a database plans and executes a query — a PARTITION BY on an unindexed column can force a full sort where an index would otherwise let the engine use pre-sorted data. On large tables, checking the query plan for window-heavy queries is worth the same attention you’d give any query touching a lot of rows, alongside general database normalization and schema design decisions that determine how much data a query actually has to scan.

Window functions are part of standard SQL and work across PostgreSQL and every other major relational database, though some frame-clause defaults and less common functions vary slightly between engines — worth checking your specific database’s documentation for edge cases like frame exclusion or NTILE tie-breaking.

The takeaway

Window functions let a query see both the individual row and its surrounding context — a partition, an order, a frame — without sacrificing one for the other. PARTITION BY groups without collapsing, ORDER BY inside OVER sequences rows for running calculations, and functions like ROW_NUMBER(), LAG(), and LEAD() solve ranking and row-comparison problems that would otherwise need subqueries or self-joins. Once you recognize a “top N per group” or “running total” shape in a requirement, a window function is almost always the cleanest way to write it.