What you will be able to do
- Break a window specification into its PARTITION BY, ORDER BY and frame parts, and say what each one controls
- Pick ranking, analytic or aggregate window functions and know the rules each class imposes
- Predict how ROWS and RANGE frames handle tied ORDER BY values, including the default frame
- Write the same window logic with the PySpark Window and WindowSpec API
Key concept
Window specification — The OVER clause that tells a window function which rows count as its group (partition), the order of those rows, and which slice of the partition (frame) to use for the current row. Unlike GROUP BY, every input row stays in the output and gets its own computed value.
1.Anatomy of a window: OVER, PARTITION BY, ORDER BY, frame
A GROUP BY aggregation turns many rows into one. A window function doesn't. It looks at a group of related rows and returns a value for every row. Databricks describes window functions as functions that operate on a group of rows and then calculate a return value for each row based on the group of rows. That's why they suit running totals, moving averages and comparisons with the previous row in event data. Collapsing those rows would throw away the detail you need.
The syntax is function OVER { window_name | ( window_name ) | window_spec }. The window spec has three optional parts, written in this order: ( [ PARTITION BY partition [ , ... ] ] [ order_by ] [ window_frame ] ).
- PARTITION BY sets the scope. Each partition is calculated separately. If you leave it out, the whole result set is one partition. - ORDER BY sorts rows inside each partition. - window_frame picks a sliding subset of the partition for aggregate and analytic functions.
Databricks also accepts Hive-style aliases. SORT BY can replace ORDER BY. DISTRIBUTE BY can replace PARTITION BY. CLUSTER BY can replace PARTITION BY, but only when there's no ORDER BY. You can also define a spec once with a window_name and reuse it.
| Function class | Examples from the docs | Constraint on OVER |
|---|---|---|
| ranking_function | RANK(), DENSE_RANK() | Must include ORDER BY; must NOT include a window_frame |
| analytic_function | LAG, LEAD, CUME_DIST | Frame applies to analytic functions |
| aggregate_function | SUM, AVG, MIN | Must not include a FILTER clause when used as a window function |
Checkpoint 1 of 5· Check yourself
An engineer writes RANK() OVER (PARTITION BY dept) to number employees within each department. What is wrong with it?
Ranking functions need ORDER BY to know what to rank, and they can't take a frame clause, so options C and D get the rule backwards.
“If specified the window_spec must include an ORDER BY clause, but not a window_frame clause.”Source: docs.databricks.com
Sources1
2.Ranking rows and reaching neighbouring rows
The two most common jobs for window functions are ranking rows within a group and comparing a row with its neighbours. The docs use one employees table (name, dept, salary, age) for both. Here, RANK numbers employees by salary inside each department, starting again at 1 for every dept.
> SELECT name,
dept,
RANK() OVER (PARTITION BY dept ORDER BY salary) AS rank
FROM employees;In the Engineering output, Tom and Chloe both earn 23000 and share rank 2. Paul comes next and gets rank 4. RANK skips a number after a tie, and DENSE_RANK doesn't. The same doc example shows Paul at 3 under DENSE_RANK.
LAG and LEAD are analytic functions that read a value from an earlier or later row in the partition's order. Their typical use is change detection, such as "how much did this value rise compared with the previous one?".
> SELECT name,
salary,
LAG(salary) OVER (PARTITION BY dept ORDER BY salary) AS lag,
LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead
FROM employees;Lisa's lag is NULL because nothing comes before her in the partition. Evan's lead is 0, not NULL, because LEAD(salary, 1, 0) sets 0 as the default when there's no following row. The doc output shows Lisa 10000 NULL 30000 and Evan 32000 30000 0.
Checkpoint 2 of 5· Check yourself
In the doc example, LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) is evaluated for the highest-paid employee in a department. What does it return?
The third argument of LEAD is the default used when no following row exists. In the doc output Evan, the top Sales earner, gets 0 where LAG-style NULL would otherwise appear.
“LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead”Source: docs.databricks.com
Sources1
3.Window frames: ROWS versus RANGE
The frame clause is frame_mode BETWEEN frame_start AND frame_end. The mode is either ROWS or RANGE. Boundaries can be UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING.
- ROWS counts physical rows. ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING covers three adjacent rows, which is the docs' moving-average example.
- RANGE sets boundaries as an offset from the current row's ORDER BY value. RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING sums every salary within 5000 of the current salary. Because of this, RANGE needs exactly one ORDER BY expression. If ORDER BY is missing or has more than one expression, Databricks raises a DATATYPE_MISMATCH error.
The docs show the difference with a side-by-side comparison:
> SELECT name,
salary,
SUM(salary) OVER (ORDER BY salary
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total,
SUM(salary) OVER (ORDER BY salary
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total
FROM employees
WHERE dept = 'Engineering';
Fred 21000 21000 21000
Chloe 23000 44000 67000
Tom 23000 67000 67000
Paul 29000 96000 96000What if you write ORDER BY without a frame? The PySpark Window reference states the default. With ordering, you get a growing frame from unbounded preceding to the current row, and that frame is a range frame. Without ordering, the frame covers the whole partition. This explains a result that surprises many people: MIN(salary) OVER (PARTITION BY dept ORDER BY salary) produces a running minimum, not one minimum per department. It also means the default running total treats ties the RANGE way. If you need strict row-by-row behaviour, write ROWS BETWEEN explicitly.
Checkpoint 3 of 5· Check yourself
Which window spec will Databricks reject?
A RANGE frame measures offsets from a single ORDER BY value, so ordering by both salary and age raises DATATYPE_MISMATCH. ROWS frames have no such limit.
“If the ORDER BY clause has multiple expressions, Databricks raises DATATYPE_MISMATCH.RANGE_FRAME_MULTI_ORDER.”Source: docs.databricks.com
Checkpoint 4 of 5· Exam question
A pipeline computes a running total of `revenue` per `store_id` ordered by `sale_ts` using: ```sql SELECT store_id, sale_ts, revenue, SUM(revenue) OVER (PARTITION BY store_id ORDER BY sale_ts) AS running_total FROM sales ``` When two rows share the exact same `sale_ts` for a store, the running total for both rows jumps to include both rows' revenue at once, instead of accumulating one row at a time. Why does this happen?
Correct answer: A — Omitting an explicit frame clause on an ordered window defaults to a RANGE frame from unbounded preceding to the current row's ORDER BY value, so peer rows sharing that value are all included together.
- A. Correct. With ORDER BY defined and no explicit frame, Spark SQL defaults to a RANGE frame from unbounded preceding to the current row, and RANGE frames include every row that ties on the ORDER BY value as peers, so all same-timestamp rows get the same, larger running total together.
- B. Incorrect. Spark does not dynamically swap frame types based on detected duplicates; the frame type is fixed by whether ORDER BY and an explicit frame clause are present, not by the data values encountered at runtime.
- C. Incorrect. Window functions never deduplicate or merge input rows; every input row is preserved in the output with its own computed value, so no row is dropped or redistributed.
- D. Incorrect. Missing a tie-breaker affects which of several equally valid row orderings Spark may choose among peers, but it does not change the frame boundary semantics that cause peers to be summed together under RANGE.
Sources2
4.The same windows in PySpark
In PySpark, the window spec is an object. Static methods on Window (partitionBy, orderBy, rowsBetween, rangeBetween) each return a WindowSpec. You pass that spec to a function's .over(...) and attach the result with withColumn. Frame boundaries use Window.unboundedPreceding, Window.unboundedFollowing and Window.currentRow, or integer offsets. rowsBetween uses row-based offsets from the current row. rangeBetween uses offsets from the current row's ORDER BY value, so it's the PySpark version of RANGE.
from pyspark.sql import Window, functions as sf
df = spark.createDataFrame(
[(1, "a"), (1, "a"), (2, "a"), (1, "b"), (2, "b"), (3, "b")], ["id", "category"]
)
# Sum id values from the current row to the next row within each partition
window = Window.partitionBy("category").orderBy("id").rowsBetween(Window.currentRow, 1)
df.withColumn("sum", sf.sum("id").over(window)).sort("id", "category", "sum").show()Checkpoint 5 of 5· Fill the gap
This sample numbers rows from 1 within each category, ordered by id. Which method creates the per-category scope?
window = Window. ? ("category").orderBy("id")
df.withColumn("row_number", sf.row_number().over(window)).show()Window.partitionBy returns a WindowSpec partitioned by category, the same as PARTITION BY in SQL. groupBy and repartition are DataFrame methods that collapse or redistribute rows. They don't build a window.
Sources3
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Adding ORDER BY to an aggregate window without a frame still aggregates over the whole partition.Why is that wrong?
When ordering is defined, the default frame grows from unbounded preceding to the current row, and it's a range frame. The result is a running aggregate in which tied values share a result.
Covered in Window frames: ROWS versus RANGE
2.ROWS and RANGE give the same running total as long as the data is sorted.Why is that wrong?
They give different results whenever ORDER BY values tie. RANGE puts all equal values in the frame together, and ROWS adds one row at a time.
Covered in Window frames: ROWS versus RANGE
3.You can add FILTER (WHERE ...) to an aggregate used as a window function, the same way you can in GROUP BY.Why is that wrong?
When an aggregate is used as a window function, it must not include a FILTER clause.
Covered in Anatomy of a window: OVER, PARTITION BY, ORDER BY, frame
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“calculate a return value for each row based on the group of rows”
↩︎ Anatomy of a window: OVER, PARTITION BY, ORDER BY, frame“If no PARTITION clause is specified the partition is comprised of all rows.”
↩︎ Anatomy of a window: OVER, PARTITION BY, ORDER BY, frame“You can use CLUSTER BY as an alias for PARTITION BY in the absence of ORDER BY.”
↩︎ Anatomy of a window: OVER, PARTITION BY, ORDER BY, frame“accessing the value of rows given the relative position of the current row”
↩︎ Ranking rows and reaching neighbouring rows“This clause defines how the rows will be grouped, sorted within the group, and which rows within a partition a function operates on.”
↩︎ Key concept“If specified the function must not include a FILTER clause.”
↩︎ Exam trap 3“If specified the window_spec must include an ORDER BY clause, but not a window_frame clause.”
↩︎ Checkpoint“LEAD(salary, 1, 0) OVER (PARTITION BY dept ORDER BY salary) AS lead”
↩︎ Checkpoint - 2.https://docs.databricks.com/aws/en/sql/language-manual/sql-ref-syntax-window-functions-frameOfficial docs
“the sliding window frame is expressed in terms of rows preceding or following the current row”
↩︎ Window frames: ROWS versus RANGE“If the ORDER BY clause is missing, Databricks raises DATATYPE_MISMATCH.RANGE_FRAME_WITHOUT_ORDER.”
↩︎ Window frames: ROWS versus RANGE“Unlike ROWS mode, RANGE groups rows with equal `ORDER BY` values together.”
↩︎ Exam trap 2“Unlike ROWS mode, RANGE groups rows with equal `ORDER BY` values together.”
↩︎ Prediction“If the ORDER BY clause has multiple expressions, Databricks raises DATATYPE_MISMATCH.RANGE_FRAME_MULTI_ORDER.”
↩︎ Checkpoint - 3.
“Creates a WindowSpec with the frame boundaries defined, from start (inclusive) to end (inclusive), using row-based offsets from the current row.”
↩︎ The same windows in PySpark“When ordering is not defined, an unbounded window frame (rowFrame, unboundedPreceding, unboundedFollowing) is used by default.”
↩︎ The same windows in PySpark“When ordering is defined, a growing window frame (rangeFrame, unboundedPreceding, currentRow) is used by default.”
↩︎ Exam trap 1