Window Functions

Window functions are the topic that separates "I can use Spark" from "I can really use Spark" — and they're the ones interviewers reach for when they want to see if you're serious.

Here's the idea. A groupBy collapses each group into one summary row. A window function computes a value across a group of rows but keeps every row. Rank each employee within their department, number each customer's orders, compute a running total — all without losing rows.

You define a window with a partition (the group) and usually an ordering:

from pyspark.sql import functions as F
from pyspark.sql.window import Window

w = Window.partitionBy("department").orderBy(F.col("salary").desc())

df.withColumn("rank", F.row_number().over(w))

partitionBy is your "group by" for the window; orderBy sets the order within each partition; and .over(w) applies a function across that window.

The functions you'll use most:

• row_number() — 1, 2, 3… strictly, no ties. The go-to for "top N per group": add row_number, then filter rank <= N.
• rank() / dense_rank() — like row_number but ties share a number (rank leaves gaps, dense_rank doesn't).
• lag(col, n) / lead(col, n) — reach back/forward n rows. Perfect for "compare each day to the previous day."
• sum/avg/count .over(w) — running totals and moving aggregates.

Ties will bite you. If two rows share the orderBy value, row_number() picks between them arbitrarily — a genuinely nondeterministic result. Whenever ties are possible, add a tiebreaker column to the ordering (orderBy(F.col("plays").desc(), F.col("title").asc())) so the answer is stable. And know your three ranking functions cold: for the same tie, row_number, rank, and dense_rank give three different answers.

The classic pattern — top N per group:

w = Window.partitionBy("genre").orderBy(F.col("plays").desc(), F.col("title").asc())
top = df.withColumn("rn", F.row_number().over(w)).filter(F.col("rn") <= 3)

Running total — and the frame that trips everyone up:

w = Window.partitionBy("account").orderBy("date") \
    .rowsBetween(Window.unboundedPreceding, Window.currentRow)
df.withColumn("running_total", F.sum("amount").over(w))

That rowsBetween is not optional decoration. An ordered window with no explicit frame defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which lumps all rows tied on the ordering key together — so two rows on the same date get the same running total instead of stepping one at a time. ROWS counts physical rows; RANGE counts by value. When the ordering key can repeat, they give different answers — reach for rowsBetween when you mean "row by row."

The mental unlock: partitionBy = which rows belong together, orderBy = in what order, the function = what to compute across them. Get those three and windows click.

Practice: chart-toppers (top N per group), dense-standings (the three ranking functions), and running-revenue (cumulative sum, ROWS vs RANGE).