intermediate

Window Functions

By DataPlayArena Technical Review BoardUpdated 2026-07-0810 min readRuntime Verified

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(), and dense_rank().
  • Analytical Functions: Calculating offsets using lead() and lag().

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:

  1. Define the Window: Specify how rows are grouped (partitionBy) and ordered (orderBy).
  2. Apply the Function: Use functions like row_number() or lead() over the defined window.

Code Examples

Input Dataset Preview

Below is the student scores dataset:

studentsubjectscore
AliceMath95
BobMath88
CharlieScience92

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:

studentsubjectscorerank
AliceMath951
BobMath882
CharlieScience921

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 use partitionBy() 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).


Interactive Challenges

Challenge 1: Lag Offset Value (Beginner)

Which window function retrieves values from a previous row at a specified offset?

Related Topics

DPA

DataPlayArena Technical Editorial Board

Curated & maintained by enterprise data platform engineers with 10+ years in distributed systems.

Runtime Verification Standards: Every code example, query, and architecture diagram in this tutorial is compiled and verified against real runtimes (Apache Spark 3.5, Python 3.11, PostgreSQL, and DuckDB). If you spot any errata or want to suggest improvements, please submit feedback.