How do window functions differ from aggregates?

An aggregate with GROUP BY collapses each group to one row. A window function computes a value across a set of related rows but keeps every input row in the output. The window is defined by the OVER() clause rather than GROUP BY.

What goes inside OVER()?

OVER() can contain PARTITION BY to split rows into independent groups, ORDER BY to order rows within each partition, and a frame clause (ROWS or RANGE) to bound which rows the function sees. Ranking and offset functions require ORDER BY; plain aggregates do not.

What is the difference between RANK and DENSE_RANK?

Both assign the same rank to tied rows, but RANK leaves gaps afterwards (1, 1, 3) while DENSE_RANK does not (1, 1, 2). ROW_NUMBER ignores ties entirely and assigns a unique sequential number to every row in order.

What does the frame clause control?

The frame (e.g. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) limits which rows an aggregate or value function sees within the ordered partition. It is how you build running totals and moving averages. ROWS counts physical rows; RANGE groups by ordering-value peers.

Does this tool send my query anywhere?

No. The function list is bundled into the page and filtered entirely in your browser. Nothing is uploaded, logged or stored.

What is the SQL Window Functions Reference?

Searchable SQL window function reference covering ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD, FIRST_VALUE and aggregate windows, with PARTITION BY, ORDER BY, frame clause and dialect support. It runs free in your browser on Gera Tools, with nothing uploaded.

SQL Window Functions Reference

Name: SQL Window Functions Reference
Creator: Gera Tools
License: https://creativecommons.org/licenses/by/4.0/

Get one useful tool a week

Like this tool? Enter your email and we'll send you one genuinely useful Gera tool a week — plus a link to come back to this one. No spam, one-click unsubscribe any time.

What window functions do

A window function performs a calculation across a set of rows related to the current row — a ranking, a running total, the previous row’s value — without collapsing the result the way GROUP BY does. Every input row stays in the output. This reference lists the ranking, offset and value functions plus the OVER() clause they all share, with per-database support.

How it works

The OVER() clause defines the window: how rows are partitioned, ordered, and framed:

SELECT name, department, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
       RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
       LAG(salary)  OVER (PARTITION BY department ORDER BY salary DESC) AS prev,
       SUM(salary)  OVER (PARTITION BY department
                          ORDER BY salary
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM   employees;

PARTITION BY resets the calculation per group, ORDER BY orders rows within each partition, and the frame clause bounds the rows an aggregate or value function sees. Ranking and offset functions require ORDER BY. The tool filters the table as you type and flags dialect support.

Choosing between ROW_NUMBER, RANK, and DENSE_RANK

These three ranking functions look similar but handle ties differently — a detail that matters whenever you want “the top 3 per group” or “the most recent entry per user”:

Function	Tie behaviour	Example output (scores 90, 90, 80)
`ROW_NUMBER()`	Assigns unique numbers, breaks ties arbitrarily	1, 2, 3
`RANK()`	Same rank for ties, then skips numbers	1, 1, 3
`DENSE_RANK()`	Same rank for ties, no gaps	1, 1, 2

Use ROW_NUMBER() when you need exactly one row per partition (for example, the latest order per customer). Use DENSE_RANK() when you want “everyone in the top 3 positions” including all tied entries.

Frame clauses for running totals and moving averages

The frame clause controls which rows an aggregate window sees relative to the current row. Without an explicit frame, an ordered window defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — which gives a running total but groups peer rows:

-- Running total (default frame):
SUM(amount) OVER (ORDER BY txn_date)

-- 7-day moving average (explicit ROWS frame):
AVG(daily_revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

ROWS counts physical rows; RANGE groups rows with identical ORDER BY values. For time-series moving averages, always use ROWS to get a predictable count.

Tips and notes

Window functions run after WHERE/GROUP BY — you cannot filter on them in WHERE; use a subquery or QUALIFY (where supported).
ROWS frames count physical rows; RANGE frames group peer rows with equal ORDER BY values.
The default frame for an ordered window is RANGE UNBOUNDED PRECEDING TO CURRENT ROW.
LAG/LEAD take optional offset and default arguments: LAG(x, 2, 0).
Named windows (WINDOW w AS (PARTITION BY department ORDER BY salary)) avoid repeating the same OVER() clause on every function in a query.