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 BYdivides the data into independent groups, such as one group per account.ORDER BYestablishes the order used for ranking, offsets, or cumulative calculations.- A frame clause such as
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWlimits 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 BYbehavior. 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
- Basic Accounting Concepts for Reading a Balance Sheet
- Google Sheets SUBTOTAL Formula: Filtered Totals
- ZP Balance of Payments: Meaning and Key Concepts
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- SUMIFS in Excel: Sum Values by Multiple Criteria ExplainedSUMIFS in Excel adds values only when they meet one or more conditions. For example, with sales in…
- Excel Filter by Text Length: Steps and FormulasTo filter Excel rows by the number of characters in a text cell, add a helper column with…
- SUMIF Criteria Examples: Text, Dates, Numbers, and MoreIn Excel, SUMIF adds values that match one condition; for example, with categories in A2:A7 and amounts in…
- Ignore Divide by Zero Errors in Excel with IFERRORTo hide a divide-by-zero result in Excel, wrap the division in IFERROR, such as =IFERROR(B2/C2,""). This displays a…
- Excel INDIRECT: Link to Another Tab with a FormulaTo refer to a cell on another worksheet using a sheet name stored in A1, enter =INDIRECT("'"&A1&"'!B2"). If…
- How to Copy Formulas in Excel to a New Workbook SafelyTo copy formulas into a new Excel workbook, open both workbooks, copy the formula cells in the source,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.