Databricks SQL Window Functions: Syntax and Examples

Formulas & functions · Updated November 22, 2026

Databricks SQL window functions calculate a value across related rows while keeping each input row in the result. Use the OVER clause to define which rows belong together, their order, and—when needed—the frame of rows used for each calculation.

What a window function does

A window function performs a calculation over a set of rows associated with the current row. Unlike GROUP BY, it does not combine those rows into one output row per group. For example, you can return every sale and also show the running sales total for that account, or retain every employee while adding their rank within a department.

The window is defined in the function’s OVER (...) clause. Its parts are optional, but commonly include:

  • PARTITION BY divides the data into independent groups, such as one group per account.
  • ORDER BY establishes the order used for ranking, offsets, or cumulative calculations.
  • A frame clause such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits which ordered rows contribute to the calculation.

Databricks SQL uses Spark SQL-style window syntax. The same broad approach applies to ranking, navigation, and aggregate calculations, but the exact function and frame matter: a running total and a three-row moving average use different windows even when they operate on the same table.

Basic syntax and a running-total example

The general pattern is function_name(arguments) OVER (PARTITION BY ... ORDER BY ... frame). The following query assumes a table named sales with account_id, sale_date, sale_id, and numeric amount columns:

SELECT
  account_id,
  sale_date,
  sale_id,
  amount,
  SUM(amount) OVER (
    PARTITION BY account_id
    ORDER BY sale_date, sale_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_amount
FROM sales;

For each account, the window starts at its first ordered sale and ends at the current sale. The result therefore includes each original sale row alongside the cumulative amount. Including sale_id makes the order reproducible when multiple sales share a date; use a column or combination of columns that uniquely breaks ties when the calculation depends on row order.

Without PARTITION BY, the window can cover the whole query result. Without ORDER BY, there is no meaningful sequence for a running total, although an aggregate window can still calculate a value across the full partition. A window’s ordering also does not guarantee the final display order of query results. Add an outer ORDER BY when the output itself must be sorted.

Partitioning, ordering, and frames

PARTITION BY resets a calculation at group boundaries without removing rows. In a sales report, partitioning by account_id makes each account’s running total start over. Partitioning by both region and account creates a separate window for every region-account combination. If a partition key is null, its null-valued rows are grouped together for the window calculation.

ORDER BY inside OVER controls the sequence used by the function; it is distinct from the query’s final sort. Ties are important. If two rows have the same ordering value, functions such as ROW_NUMBER may assign their positions in an unspecified order unless more columns resolve the tie. An order like ORDER BY sale_date, sale_id is more reliable than ordering by date alone when the identifier is unique.

A frame determines the portion of the partition considered for the current row. ROWS counts physical rows in the ordered sequence. For example, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW includes the current row and up to two earlier rows. A frame can be bounded, as in a moving calculation, or extend from UNBOUNDED PRECEDING to the current row, as in a cumulative calculation.

RANGE is based on ordering values and peer rows rather than simply counting a fixed number of row positions. This distinction matters when ordering values repeat: peers with the same ordering value can be treated together. For predictable row-by-row running totals, explicitly use a ROWS frame and a tie-breaking sort key. Databricks SQL also applies a default frame when you omit one: with an ORDER BY, the default is a range from unbounded preceding through the current row; without an ordering clause, the frame covers the full partition. Writing the intended frame explicitly avoids surprises, especially with duplicate sort values and functions such as LAST_VALUE.

Ranking and top-N calculations

Ranking functions answer related but different questions. ROW_NUMBER() assigns a distinct sequential number to every row. RANK() gives tied rows the same rank and leaves gaps after a tie. DENSE_RANK() also gives ties the same rank, but the next rank has no gap. NTILE(n) divides ordered rows into up to n buckets, which can help create roughly equal-sized groups.

For example, the following returns the three highest-value sales per account. The outer query filters the window result, so the window calculation occurs before that filter:

WITH ranked_sales AS (
  SELECT
    account_id,
    sale_id,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY account_id
      ORDER BY amount DESC, sale_id
    ) AS row_num
  FROM sales
)
SELECT account_id, sale_id, amount, row_num
FROM ranked_sales
WHERE row_num <= 3;

This returns at most three rows per account. Because the query uses ROW_NUMBER, it selects exactly three when at least three rows exist, even if amounts tie; sale_id decides which tied rows come first. If the requirement is to include every sale tied at the third-place amount, use RANK() or DENSE_RANK() instead and choose the tie behavior deliberately. The number of returned rows may then exceed three.

Aggregate and moving-window calculations

Aggregate functions such as SUM, AVG, COUNT, MIN, and MAX can be used as window functions. They return a result for each row in the window rather than collapsing the group. A full-partition average, for example, can place each row’s value beside the average for its account. A cumulative COUNT(*) can number how many rows have appeared so far when paired with an explicit ordered frame.

A moving average over the current sale and the six preceding rows can be written as:

SELECT
  account_id,
  sale_date,
  sale_id,
  amount,
  AVG(amount) OVER (
    PARTITION BY account_id
    ORDER BY sale_date, sale_id
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS seven_sale_average
FROM sales;

This is a seven-row moving average, not necessarily a seven-day average. At the beginning of an account’s history, fewer than seven rows are available, so the frame includes only the available rows. Also, aggregate functions generally ignore null input values, while COUNT(*) counts rows. Decide whether missing amounts should be excluded, replaced, or handled separately; do not assume a null amount is equivalent to zero.

Navigation and value functions

LAG and LEAD read a value from a preceding or following row in the ordered partition. They are useful for comparing a sale with the previous sale, finding changes between events, or identifying gaps. For example:

SELECT
  account_id,
  sale_date,
  sale_id,
  amount,
  LAG(amount) OVER (
    PARTITION BY account_id
    ORDER BY sale_date, sale_id
  ) AS previous_amount
FROM sales;

The first row of each account has no preceding amount, so the result is null unless a default is supplied to LAG. A null result can therefore mean “no previous row,” but it may also mean the previous row’s amount was itself null. If that distinction matters, include a row identifier or use an explicit default thoughtfully.

FIRST_VALUE, LAST_VALUE, and NTH_VALUE return values from rows in the window. Be especially careful with LAST_VALUE: if the frame ends at the current row, it usually returns the last value in that frame—not the last value in the entire partition. To obtain the partition’s final value for every row, define a frame that extends through UNBOUNDED FOLLOWING, while retaining the intended order. Check how null values should be treated for the chosen function and data; null-handling options and function syntax are documented by Databricks.

Filtering window results

A window function is evaluated after the query’s WHERE filtering and grouping stages, so it cannot normally be placed directly in the same query block’s WHERE clause. Use a subquery or common table expression, as in the top-N example, to calculate the window value first and filter it in the outer query.

Databricks SQL also supports QUALIFY for filtering rows based on window-function results. For example, after the FROM clause, a query can use QUALIFY ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC, sale_id) <= 3. This can make top-N queries shorter. A CTE is still useful when you want to inspect, reuse, or return the calculated rank, or when writing SQL intended to work in environments that do not support QUALIFY. Check the SQL features available in the target Databricks environment if portability or older-runtime compatibility matters.

Performance and common mistakes

Window calculations can require data to be organized by partition and sort keys, which can involve substantial work on large tables. A useful query should limit input rows and columns before the window when that does not change the intended result. For example, filtering to a relevant date range before calculating a window can reduce work, but it also changes the rows visible to that window and can alter running totals or lagged values. Confirm that the filter is logically valid, not merely convenient.

  • Choose partition keys that match the calculation. Omitting a needed key can mix unrelated entities; adding unnecessary keys can reset results too often.
  • Use a stable order for row-sensitive calculations. Add a unique tie-breaker when repeated dates or scores are possible.
  • Specify a frame for cumulative and moving aggregates. An omitted frame may not express the intended business rule, especially when order values repeat.
  • Do not expect GROUP BY behavior. Window functions keep detail rows, so the output can be much larger than an aggregate report.
  • Check nulls, duplicate records, and boundary rows. These often explain differences between a query’s results and a business expectation.

Very large or uneven partitions can also make execution less efficient because some groups contain far more rows than others. Review the query plan and runtime metrics in the Databricks environment when performance is a concern; avoid assuming that adding a partition key purely to distribute work is safe, since it changes the calculation’s meaning. Compute use depends on the SQL warehouse, workload, and execution choices, so there is no universal cost per window function.

Window functions versus alternatives

Use GROUP BY when the desired result is one row per group, such as total sales per account. Use a window function when you need a group-level value alongside detail rows, or need ordered logic such as ranking, previous-row comparisons, or moving averages. A self-join can sometimes express a related calculation, but it is typically more verbose and can multiply rows if join keys are not unique. For straightforward ranking or cumulative calculations, a window function communicates the intent directly.

Some tasks are not inherently window problems. For example, a simple total per category is clearer as a grouped aggregate, and deduplication may be better expressed with a documented key and ranking rule. If choosing one representative row per group, define what “best” means and provide deterministic ordering before filtering to row number one. Window functions supply a flexible tool, not a substitute for a clear definition of the desired result.

Frequently Asked Questions

What does OVER mean in Databricks SQL?
The OVER clause defines the rows a window function can use for the current row. It can specify partitions, ordering, and a frame.

What is the difference between GROUP BY and a window function?
GROUP BY combines rows into groups and returns summary rows. A window function calculates across related rows but normally leaves each input row in the result.

How do I get the top three rows per group in Databricks SQL?
Use ROW_NUMBER() with PARTITION BY for the group and an ordered sort that defines the winners, then filter to row numbers at most three in an outer query or with QUALIFY. Use a ranking function instead if ties should all be included.

Why does LAST_VALUE return the current row’s value?
The function returns the last value in its current frame. If the frame ends at the current row, that is often the current row; extend the frame to UNBOUNDED FOLLOWING when you need the partition’s final value.

Do Databricks window functions require an ORDER BY clause?
Not all of them. A full-partition aggregate can work without ordering, but row ranking, lag/lead, and sequence-based calculations need an order to have a meaningful result. For reproducible row-level results, make that order deterministic.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.