Topic 10 of 28 · Advanced
Window Functions
row_number, rank, lag/lead and running totals.
Concept
A Window spec defines partitioning and ordering. Window functions compute values per row using related rows, without collapsing them.
row_number for de-duplication/latest record, rank/dense_rank for top-N, lag/lead for change over time, sum over rowsBetween for running totals.
A window without partitionBy moves all data to one partition — avoid on large data.
Code (PySpark 3.5)
from pyspark.sql.window import Window
w = Window.partitionBy("customer_id").orderBy(F.desc("order_date"))
latest = (orders.withColumn("rn", F.row_number().over(w))
.filter("rn = 1").drop("rn"))
wr = Window.partitionBy("city").orderBy("order_date") \
.rowsBetween(Window.unboundedPreceding, Window.currentRow)
running = orders.withColumn("running_revenue", F.sum("amount").over(wr)) \
.withColumn("prev_amount", F.lag("amount").over(
Window.partitionBy("city").orderBy("order_date")))Remember
- row_number = dedupe / latest record.
- Always partition windows on big data.
- lag/lead need an orderBy.
Interview questions
How do you keep only the latest record per key?
row_number() over a window partitioned by key and ordered by timestamp desc, then filter rn = 1.
rank vs dense_rank?
rank leaves gaps after ties (1,1,3); dense_rank doesn't (1,1,2).
Lesson test
Take an 8-question test on “Window Functions” — concepts, output prediction, bug spotting and interview questions.