--- cuppy_hub: salesforce cuppy_path: reference/official/data360/data-cloud-query-guide/references/dc-sql-reference/window.md cuppy_section: docs cuppy_doc: 90M2-QD0D cuppy_doc_uuid: 7950c68f-bea8-440a-982a-899070b77e3a cuppy_permalink: https://cuppy.io/salesforce/d/90M2-QD0D cuppy_version: 1 cuppy_updated: 2026-10-23T16:16:52.575Z cuppy_canonical: https://cuppy.io/salesforce/docs/reference/official/data360/data-cloud-query-guide/references/dc-sql-reference/window cuppy_mcp: https://cuppy.io/mcp cuppy_body_sha256: 23862eed59830b993c0b201d544e10dcb09da5dcf1ab0320edb3154176acd2e8 # cuppy_body_sha256 is over the document body ALONE, excluding both generated blocks. --- > Mirror of (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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-window.html). A window function call includes an `OVER` clause. Specify a named window or define a window inline. An inline `` 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 `` defines which rows in the partition a frame-sensitive function can access. Its syntax is: `` and `` can be `UNBOUNDED PRECEDING`, ` PRECEDING`, `CURRENT ROW`, ` FOLLOWING`, or `UNBOUNDED FOLLOWING`. If `` 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 `` 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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/cume-dist.html) - Returns the cumulative distribution of the current row and its peers. * [DENSE_RANK](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/dense-rank.html) - Returns the rank of the current row without gaps. * [MODIFIED_RANK](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/modified-rank.html) - Returns the rank of the current row with gaps and uses the last peer’s rank for ties. * [NTILE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/ntile.html) - Divides a partition into a specified number of roughly equal buckets. * [PERCENT_RANK](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/percent-rank.html) - Returns the relative rank of the current row as a value between 0 and 1. * [RANK](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/rank.html) - Returns the rank of the current row with gaps. * [ROW_NUMBER](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/row-number.html) - Returns the current row number within its partition, beginning at 1. * [FIRST_VALUE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/first-value.html) - Returns the value from the first row of the window frame. * [LAG](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/lag.html) - Returns a value from a row before the current row. * [LAST_VALUE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/last-value.html) - Returns the value from the last row of the window frame. * [LEAD](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/lead.html) - Returns a value from a row after the current row. * [NTH_VALUE](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/nth-value.html) - 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](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.html) 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. * [SELECT](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/select-window.html) * [Aggregate Functions](https://developer.salesforce.com/docs/data/data-cloud-query-guide/references/dc-sql-reference/aggregate.html) --- You are reading one file. The same content is free over MCP at https://cuppy.io/mcp with no signup: ranked search across all 376 documents in this hub, the merged view when you work in a layer, known workarounds matched to the error you hit, and depth control so you spend tokens on what you need. Start with the `_hello` tool. How to connect: https://cuppy.io/salesforce/ Something wrong in this document? Report it with the `session feedback` tool on the same endpoint.