intermediate
Window Functions
10 min readLast updated: 2026-07-08
Overview
Learn how to perform calculations across a partition of rows—such as running totals, ranks, or moving averages—without collapsing the rows.
What You Will Learn
In this lesson, you will learn:
- Window Definition: Partitioning and ordering rows using
Window.partitionBy(). - Ranking Functions: Assigning ranks using
row_number(),rank(), anddense_rank(). - Analytical Functions: Calculating offsets using
lead()andlag().
Detailed Concept Explanation
A Window Function performs calculations across a set of rows (called a frame) that are related to the current row. Unlike a groupBy(), which collapses multiple rows into a single summary row, window functions return a value for every input row.
To use a window function:
- Define the Window: Specify how rows are grouped (
partitionBy) and ordered (orderBy). - Apply the Function: Use functions like
row_number()orlead()over the defined window.
Code Examples
Input Dataset Preview
Below is the student scores dataset:
| student | subject | score |
|---|---|---|
| Alice | Math | 95 |
| Bob | Math | 88 |
| Charlie | Science | 92 |
Python (PySpark) Implementation
python
from pyspark.sql import SparkSession
from pyspark.sql.window import Window
from pyspark.sql.functions import col, row_number
spark = SparkSession.builder.appName("Window").getOrCreate()
data = [("Alice", "Math", 95), ("Bob", "Math", 88), ("Charlie", "Science", 92)]
df = spark.createDataFrame(data, ["student", "subject", "score"])
# Define window specification
windowSpec = Window.partitionBy("subject").orderBy(col("score").desc())
# Calculate rank per subject
ranked_df = df.withColumn("rank", row_number().over(windowSpec))
ranked_df.show()
Expected Output
text
+-------+-------+-----+----+
|student|subject|score|rank|
+-------+-------+-----+----+
| Alice| Math| 95| 1|
| Bob| Math| 88| 2|
|Charlie|Science| 92| 1|
+-------+-------+-----+----+
Execution Plan Diagram (Python & Scala)
Execution Plan Diagram
SparkSession.builder
createDataFrame
Window.partitionBy(subject).orderBy(score)
withColumn(rank
row_number().over)
show()
Scala Implementation
scala
import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._
val spark = SparkSession.builder().appName("WindowScala").getOrCreate()
import spark.implicits._
val df = Seq(("Alice", "Math", 95), ("Bob", "Math", 88), ("Charlie", "Science", 92)).toDF("student", "subject", "score")
val windowSpec = Window.partitionBy("subject").orderBy($"score".desc)
val ranked = df.withColumn("rank", row_number().over(windowSpec))
ranked.show()
Expected Output
text
+-------+-------+-----+----+
|student|subject|score|rank|
+-------+-------+-----+----+
| Alice| Math| 95| 1|
| Bob| Math| 88| 2|
|Charlie|Science| 92| 1|
+-------+-------+-----+----+
SQL Implementation
sql
SELECT student, subject, score,
ROW_NUMBER() OVER(PARTITION BY subject ORDER BY score DESC) AS rank
FROM grades;
Expected Output
Executing this SQL query returns:
| student | subject | score | rank |
|---|---|---|---|
| Alice | Math | 95 | 1 |
| Bob | Math | 88 | 2 |
| Charlie | Science | 92 | 1 |
Common Mistakes
- Omission of partitionBy: Defining a window with only an
orderBy()clause forces Spark to gather all dataset rows onto a single node to determine the overall order. This will crash your driver with an Out Of Memory (OOM) error. Always usepartitionBy()to distribute the window calculations.
Best Practices
- Define Window Limits: Use frame boundaries (like
rowsBetween) to limit calculations to a subset of rows relative to the current row, protecting memory.
Interview Perspective
What is the difference between rank() and dense_rank() in Spark?
Both assign a rank to rows based on an order. However, if there is a tie (e.g. two rows share rank 1), rank() will skip the next rank (assigning the third row rank 3), while dense_rank() will not skip ranks (assigning the third row rank 2).