Mirror of <https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/window.html> (Salesforce, Data 360 Query Guide), read 2026-10-23. Salesforce's own page is the authority; this copy exists because that site refuses non-browser readers.

Window Functions

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Window functions calculate a value across related rows while preserving one result row for each input row. Use window functions to rank rows, compare a row with values in its partition, and calculate running or partition-level aggregates.

Add an OVER clause after a window function or aggregate function call. The OVER clause defines the rows that the function uses for each result row.

This query returns every employee row and calculates the average salary for that employee’s department.

Use these clauses in an OVER clause to define a window.

PARTITION BY divides query results into partitions. This function calculates a result separately for each partition. Omit PARTITION BY to use all result rows as one partition.
ORDER BY defines the order in which the function processes rows in a partition. This ordering is independent of the top-level ORDER BY clause that sorts the final query results.
A frame clause limits aggregate window functions to a subset of rows in the current partition. Use ROWS or RANGE with BETWEEN, UNBOUNDED PRECEDING, CURRENT ROW, and UNBOUNDED FOLLOWING to define the frame.

For example, this query assigns a rank to each employee within their department, ordered from highest to lowest salary.

When you use ORDER BY in a window definition, rows with equal ordering values are peers. Ranking functions return the same rank for peer rows.

Window frames apply to functions that operate on a subset of the current partition, including aggregate functions used with an OVER clause. When an OVER clause includes ORDER BY, the default frame starts at the beginning of the partition and ends at the current row and its peers. Without ORDER BY, the default frame includes the entire partition.

This query calculates a running total of salary values.

To calculate over the entire partition while retaining ORDER BY, specify a frame that starts with the first row and ends with the last row.

Use the WINDOW clause to name a window definition that multiple functions share.

For the complete WINDOW clause syntax, see SELECT.

A window function call includes an OVER clause. Specify a named window or define a window inline.

An inline <window_definition> has this syntax:

Expressions in PARTITION BY and ORDER BY can’t contain window function calls. Without PARTITION BY, all rows in the query result belong to one partition. Without ORDER BY, rows are processed in an unspecified order.

The optional <frame_clause> defines which rows in the partition a frame-sensitive function can access. Its syntax is:

<frame_start> and <frame_end> can be UNBOUNDED PRECEDING, <offset> PRECEDING, CURRENT ROW, <offset> FOLLOWING, or UNBOUNDED FOLLOWING. If <frame_end> is omitted, it defaults to CURRENT ROW.

In ROWS mode, CURRENT ROW refers to the current row. In RANGE mode, CURRENT ROW refers to the current row’s peer group: the frame starts at the first peer and ends at the last peer. A peer is a row with equivalent values in the window’s ORDER BY expressions.

In ROWS mode, an offset is a nonnegative integer number of rows before or after the current row. In RANGE mode, the window must have exactly one ORDER BY expression; the offset is a nonnegative value compatible with that expression’s type and defines the maximum value difference for included rows.

The optional <frame_exclusion> can be EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, or EXCLUDE NO OTHERS. These options exclude the current row, current row and its peers, peers other than the current row, or no rows, respectively.

The default frame is RANGE UNBOUNDED PRECEDING, equivalent to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With ORDER BY, it includes rows from the start of the partition through the current row’s last peer. Without ORDER BY, it includes the entire partition. Frame-sensitive functions such as first_value, last_value, and nth_value use this frame; lag and lead use partition order instead.

CUME_DIST - Returns the cumulative distribution of the current row and its peers.
DENSE_RANK - Returns the rank of the current row without gaps.
MODIFIED_RANK - Returns the rank of the current row with gaps and uses the last peer’s rank for ties.
NTILE - Divides a partition into a specified number of roughly equal buckets.
PERCENT_RANK - Returns the relative rank of the current row as a value between 0 and 1.
RANK - Returns the rank of the current row with gaps.
ROW_NUMBER - Returns the current row number within its partition, beginning at 1.

FIRST_VALUE - Returns the value from the first row of the window frame.
LAG - Returns a value from a row before the current row.
LAST_VALUE - Returns the value from the last row of the window frame.
LEAD - Returns a value from a row after the current row.
NTH_VALUE - Returns the value from the specified row of the window frame.

Most aggregate functions become window functions when you add an OVER clause. For example, use sum(amount) OVER (...) to calculate a running total or a total for each partition. See Aggregate Functions for the supported aggregate functions.

Use window functions only in the SELECT list or the top-level ORDER BY clause.
Window functions run after WHERE, GROUP BY, and HAVING filter the query’s input rows.
Window functions run after non-window aggregate functions. You can use an aggregate function in a window function argument, but you can’t use a window function in an aggregate function argument.
Use a subquery to filter rows based on a window-function result.