Browse cheat sheets · PySpark
Window Functions
Use PySpark window functions safely for ranking, latest-row selection, running totals, lag/lead analysis, and partitioned calculations without collapsing DataFrame grain.
On this page
- Problem
- Quick answer
- Code / commands
- 1. Import Window and Spark functions
- 2. Assign a row number within each group
- 3. Reproduce SQL ROW_NUMBER()
- 4. Keep the latest row per business key
- 5. Keep top-N rows per group
- 6. Use rank() when ties share the same rank
- 7. Use dense_rank() when ties should not create gaps
- 8. Compare row_number, rank, and dense_rank
- 9. Get the previous row value with lag()
- 10. Get the next row value with lead()
- 11. Calculate time since previous event
- 12. Detect value changes
- 13. Build a running total
- 14. Build a running count
- 15. Build a running maximum
- 16. Use a rolling row-count window
- 17. Use rangeBetween() for value-based frames
- 18. Understand rowsBetween() vs rangeBetween()
- 19. Calculate partition total without reducing rows
- 20. Calculate percentage of group total
- 21. Calculate partition average
- 22. Compare row value to group average
- 23. Use first_value() with an ordered window
- 24. Use last_value() with an explicit full-partition frame
- 25. Get the first non-null value
- 26. Use nth_value() where supported and appropriate
- 27. Use cume_dist() for cumulative distribution
- 28. Use percent_rank() for relative ranking
- 29. Use ntile() for bucket assignment
- 30. Build effective-date ranges with lead()
- 31. Create session boundaries from event gaps
- 32. Assign session IDs with a cumulative sum
- 33. Deduplicate with row_number() before a MERGE
- 34. Avoid global windows unless they are truly required
- 35. Keep partitions aligned with the business entity
- 36. Check partition skew before large windows
- 37. Do not replace null partition keys with one global sentinel casually
- 38. Reuse the same window specification
- 39. Inspect the physical plan
- 40. Filter before a large window when safe
- What the result means
- What to look for
- Latest row changes between runs
- last_value() equals the current row unexpectedly
- Running total jumps unexpectedly
- Window stage has one very slow task
- Window causes a massive sort/shuffle
- Rolling “7-day” metric is wrong
- Production notes
- Window functions are usually sort-heavy
- Deterministic ordering matters
- Window partitions can become memory-heavy
- Filtering before a window can change semantics
- rangeBetween() requires careful ordering semantics
- Use built-in window functions before custom Python logic
- Common mistakes
- 1. Using row_number() without a complete order
- 2. Using rank() when exactly N rows are required
- 3. Using row_number() when all ties should survive
- 4. Treating first() / last() as time-aware automatically
- 5. Confusing rowsBetween() with time ranges
- 6. Using a global ordered window unnecessarily
- 7. Ignoring partition skew
- 8. Filtering history away before calculating a lifetime window
- 9. Replacing all null keys with one sentinel
- 10. Using windows when groupBy() is enough
- Next step
- Related deep dives
Problem
You need to calculate values across related rows without collapsing the DataFrame to one row per group.
Typical production use cases include:
- keep the latest row per business key
- rank products within category
- calculate running totals
- compare each row with the previous or next event
- calculate time gaps between events
- detect status changes
- calculate rolling metrics
- assign deterministic row numbers
- select top-N rows per group
The main risks are:
- incomplete ordering
- ambiguous ties
- huge window partitions
- unnecessary global sorts
- using the wrong frame semantics
- relying on
first()/last()without understanding ordering - mixing row-based and range-based frames incorrectly
The goal is to make the partition, ordering, and frame explicit.
Quick answer
For the latest row per customer:
from pyspark.sql import Window
from pyspark.sql import functions as F
w = (
Window
.partitionBy("customer_id")
.orderBy(
F.col("event_time").desc(),
F.col("event_id").desc(),
)
)
latest = (
df
.withColumn(
"_row_number",
F.row_number().over(w),
)
.filter(
F.col("_row_number") == 1
)
.drop("_row_number")
)
For a running total:
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
.rowsBetween(
Window.unboundedPreceding,
Window.currentRow,
)
)
df = df.withColumn(
"running_amount",
F.sum("amount").over(w),
)
The key rule is:
PARTITION BY
→ which rows belong together
ORDER BY
→ which row comes first
FRAME
→ which ordered rows participate in the calculation
Code / commands
1. Import Window and Spark functions
from pyspark.sql import Window
from pyspark.sql import functions as F
Use Window to define partitioning, ordering, and frames.
2. Assign a row number within each group
w = (
Window
.partitionBy("customer_id")
.orderBy(
F.col("event_time").asc()
)
)
df = df.withColumn(
"row_number",
F.row_number().over(w),
)
This gives each row a unique sequence within its partition.
Use it when ties must still receive different row numbers.
3. Reproduce SQL ROW_NUMBER()
w = (
Window
.partitionBy("merchant_id")
.orderBy(
F.col("revenue").desc(),
F.col("product_id").asc(),
)
)
ranked = df.withColumn(
"row_number",
F.row_number().over(w),
)
The second order column gives deterministic tie-breaking.
Do not rely on arbitrary physical row order.
4. Keep the latest row per business key
w = (
Window
.partitionBy("customer_id")
.orderBy(
F.col("updated_at").desc(),
F.col("source_sequence").desc(),
)
)
latest = (
df
.withColumn(
"_rn",
F.row_number().over(w),
)
.filter(F.col("_rn") == 1)
.drop("_rn")
)
This is the preferred pattern when the surviving row matters.
5. Keep top-N rows per group
w = (
Window
.partitionBy("category")
.orderBy(
F.col("revenue").desc(),
F.col("product_id").asc(),
)
)
top_3 = (
df
.withColumn(
"_rn",
F.row_number().over(w),
)
.filter(
F.col("_rn") <= 3
)
.drop("_rn")
)
This keeps exactly up to N rows per partition.
If ties should all survive, use rank() or dense_rank() instead.
6. Use rank() when ties share the same rank
w = (
Window
.partitionBy("category")
.orderBy(
F.col("revenue").desc()
)
)
df = df.withColumn(
"rank",
F.rank().over(w),
)
Example values:
100
100
90
80
produce ranks:
1
1
3
4
7. Use dense_rank() when ties should not create gaps
df = df.withColumn(
"dense_rank",
F.dense_rank().over(w),
)
For the same values:
100
100
90
80
dense ranks are:
1
1
2
3
Use the rank method that matches the business definition.
8. Compare row_number, rank, and dense_rank
Use:
row_number
→ every row gets a unique sequence
rank
→ ties share rank; gaps remain
dense_rank
→ ties share rank; no gaps
Do not substitute one for another without checking tie semantics.
9. Get the previous row value with lag()
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
)
df = df.withColumn(
"previous_status",
F.lag(
"status",
1,
).over(w),
)
Use this for:
- status transitions
- previous amount
- previous timestamp
- change detection
10. Get the next row value with lead()
df = df.withColumn(
"next_status",
F.lead(
"status",
1,
).over(w),
)
Use this for:
- next event analysis
- effective-date ranges
- future-state comparisons
11. Calculate time since previous event
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
)
df = (
df
.withColumn(
"previous_event_time",
F.lag("event_time").over(w),
)
.withColumn(
"seconds_since_previous",
F.col("event_time").cast("long")
- F.col("previous_event_time").cast("long"),
)
)
This is useful for:
- sessionization
- repeat-request analysis
- latency
- event-gap detection
Make sure timestamp ordering is semantically correct.
12. Detect value changes
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
)
df = (
df
.withColumn(
"previous_status",
F.lag("status").over(w),
)
.withColumn(
"status_changed",
F.when(
F.col("previous_status").isNull(),
F.lit(False),
).otherwise(
~F.col("status").eqNullSafe(
F.col("previous_status")
)
),
)
)
Use eqNullSafe() when null equality semantics matter.
13. Build a running total
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
.rowsBetween(
Window.unboundedPreceding,
Window.currentRow,
)
)
df = df.withColumn(
"running_amount",
F.sum("amount").over(w),
)
This includes all prior rows in the partition through the current row.
14. Build a running count
df = df.withColumn(
"running_count",
F.count("*").over(w),
)
Useful for:
- event sequence
- cumulative activity
- progressive counters
15. Build a running maximum
df = df.withColumn(
"running_max_amount",
F.max("amount").over(w),
)
This gives the largest value seen so far within the ordered partition.
16. Use a rolling row-count window
Current row plus previous six rows:
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
.rowsBetween(
-6,
0,
)
)
df = df.withColumn(
"rolling_7_row_avg",
F.avg("amount").over(w),
)
This means:
7 rows
not:
7 days
Do not confuse row-based windows with time-based windows.
17. Use rangeBetween() for value-based frames
Suppose the order column is epoch seconds:
df = df.withColumn(
"event_epoch",
F.col("event_time").cast("long"),
)
A 7-day trailing window:
seconds_7_days = 7 * 24 * 60 * 60
w = (
Window
.partitionBy("customer_id")
.orderBy("event_epoch")
.rangeBetween(
-seconds_7_days,
0,
)
)
df = df.withColumn(
"rolling_7_day_amount",
F.sum("amount").over(w),
)
This frame is based on the numeric order-column value, not number of rows.
Use this carefully and document the unit.
18. Understand rowsBetween() vs rangeBetween()
Use:
rowsBetween()
→ relative physical row positions
rangeBetween()
→ relative values of the ORDER BY expression
Example:
rowsBetween(-6, 0)
means seven rows.
Example:
rangeBetween(-604800, 0)
on epoch seconds means the previous seven days.
They are not interchangeable.
19. Calculate partition total without reducing rows
w = (
Window
.partitionBy("merchant_id")
)
df = df.withColumn(
"merchant_total",
F.sum("amount").over(w),
)
Unlike groupBy(), this keeps the original row grain.
Use this when each row needs both:
- row-level detail
- group-level metric
20. Calculate percentage of group total
w = (
Window
.partitionBy("merchant_id")
)
df = (
df
.withColumn(
"merchant_total",
F.sum("amount").over(w),
)
.withColumn(
"pct_of_merchant",
F.when(
F.col("merchant_total") != 0,
F.col("amount")
/ F.col("merchant_total"),
),
)
)
This keeps each source row and adds its share of the partition total.
21. Calculate partition average
w = (
Window
.partitionBy("merchant_id")
)
df = df.withColumn(
"merchant_avg_amount",
F.avg("amount").over(w),
)
Again, the original row grain remains intact.
22. Compare row value to group average
df = (
df
.withColumn(
"merchant_avg_amount",
F.avg("amount").over(w),
)
.withColumn(
"above_merchant_avg",
F.col("amount")
> F.col("merchant_avg_amount"),
)
)
This is useful for within-group comparisons without a separate aggregation + join.
23. Use first_value() with an ordered window
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
.rowsBetween(
Window.unboundedPreceding,
Window.unboundedFollowing,
)
)
df = df.withColumn(
"first_status",
F.first_value("status").over(w),
)
Use an explicit frame when you want the value across the full partition.
24. Use last_value() with an explicit full-partition frame
df = df.withColumn(
"last_status",
F.last_value("status").over(w),
)
Without an appropriate frame, last_value() can return the current row’s value rather than the final value in the partition.
This is one of the most common window-function mistakes.
25. Get the first non-null value
df = df.withColumn(
"first_non_null_status",
F.first(
"status",
ignorenulls=True,
).over(w),
)
Be explicit about whether nulls should be ignored.
26. Use nth_value() where supported and appropriate
Example:
df = df.withColumn(
"second_status",
F.nth_value(
"status",
2,
).over(w),
)
Use this when a fixed ordered position has business meaning.
If you need row-level selection, row_number() is often clearer.
27. Use cume_dist() for cumulative distribution
w = (
Window
.partitionBy("category")
.orderBy(
F.col("revenue").asc()
)
)
df = df.withColumn(
"cume_dist",
F.cume_dist().over(w),
)
This shows the relative position of each row within the ordered partition.
Useful for distribution analysis.
28. Use percent_rank() for relative ranking
df = df.withColumn(
"percent_rank",
F.percent_rank().over(w),
)
This returns a value from 0 to 1 based on rank position.
Do not confuse it with a statistical percentile value.
29. Use ntile() for bucket assignment
w = (
Window
.partitionBy("region")
.orderBy(
F.col("revenue").desc()
)
)
df = df.withColumn(
"quartile",
F.ntile(4).over(w),
)
This divides ordered rows into approximately equal-sized buckets.
Do not use it as a substitute for exact percentile boundaries when value-based thresholds matter.
30. Build effective-date ranges with lead()
w = (
Window
.partitionBy("customer_id")
.orderBy("effective_from")
)
history = df.withColumn(
"effective_to",
F.lead(
"effective_from"
).over(w),
)
This is useful for versioned records.
Decide whether effective_to is:
exclusive
or:
inclusive
and keep that convention consistent.
31. Create session boundaries from event gaps
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
)
events = (
df
.withColumn(
"previous_event_time",
F.lag("event_time").over(w),
)
.withColumn(
"gap_seconds",
F.col("event_time").cast("long")
- F.col("previous_event_time").cast("long"),
)
)
Mark a new session:
events = events.withColumn(
"new_session",
F.when(
F.col("previous_event_time").isNull()
| (F.col("gap_seconds") > 1800),
1,
).otherwise(0),
)
The 1,800-second threshold is an example.
Use a threshold defined by the actual business/session rule.
32. Assign session IDs with a cumulative sum
session_window = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
.rowsBetween(
Window.unboundedPreceding,
Window.currentRow,
)
)
events = events.withColumn(
"session_number",
F.sum("new_session").over(
session_window
),
)
This turns detected boundaries into sequential session groups.
33. Deduplicate with row_number() before a MERGE
w = (
Window
.partitionBy("sale_id")
.orderBy(
F.col("updated_at").desc(),
F.col("source_sequence").desc(),
)
)
source_deduped = (
source
.withColumn(
"_rn",
F.row_number().over(w),
)
.filter(
F.col("_rn") == 1
)
.drop("_rn")
)
This is a strong pattern for preparing deterministic Delta MERGE inputs.
34. Avoid global windows unless they are truly required
This:
w = Window.orderBy("event_time")
has no partition key.
All rows belong to one logical window.
For large DataFrames, that can force very expensive global ordering.
Use global windows only when the business question is truly global.
35. Keep partitions aligned with the business entity
Good:
Window.partitionBy("customer_id")
for per-customer event history.
Potentially expensive:
Window.partitionBy("country")
if one country contains hundreds of millions of rows and the logic could use a more specific entity key.
Choose a partition that matches both business meaning and practical data distribution.
36. Check partition skew before large windows
(
df
.groupBy("customer_id")
.count()
.orderBy(
F.desc("count")
)
.show(
50,
truncate=False,
)
)
A few enormous partitions can cause long-running window tasks.
Look for:
- one dominant customer/tenant
- null/sentinel keys
- malformed keys
- legitimate heavy users
37. Do not replace null partition keys with one global sentinel casually
Risky:
df = df.fillna(
{
"customer_id": "UNKNOWN"
}
)
Then every null-key row can land in one huge window partition.
Handle invalid/missing keys separately when possible.
38. Reuse the same window specification
Instead of rebuilding identical windows:
w = (
Window
.partitionBy("customer_id")
.orderBy("event_time")
)
then:
df = (
df
.withColumn(
"previous_amount",
F.lag("amount").over(w),
)
.withColumn(
"next_amount",
F.lead("amount").over(w),
)
)
This keeps code easier to review.
Spark may also combine compatible window operations in the physical plan.
39. Inspect the physical plan
result.explain("formatted")
Look for:
WindowExchangeSort
Window operations commonly require repartitioning by partition keys and sorting by order keys.
Do not assume a window is cheap just because it adds one column.
40. Filter before a large window when safe
Prefer:
recent = (
df
.filter(
F.col("event_date") >= "2026-10-01"
)
.select(
"customer_id",
"event_time",
"event_id",
"amount",
)
)
then apply the window.
This can reduce shuffle and sort volume.
But do not filter away earlier rows if the window logic needs historical context.
For example, a running lifetime total requires earlier history.
What the result means
| Function | Meaning |
|---|---|
row_number() |
Unique sequential number within each ordered partition |
rank() |
Ties share rank; gaps remain |
dense_rank() |
Ties share rank; no gaps |
lag() |
Value from a previous row |
lead() |
Value from a following row |
sum().over() |
Running/partitioned sum without reducing grain |
avg().over() |
Partition/window average without reducing grain |
first_value() |
First value in the defined ordered frame |
last_value() |
Last value in the defined ordered frame |
ntile(n) |
Approximate equal-sized row buckets |
cume_dist() |
Cumulative distribution position |
percent_rank() |
Relative rank from 0 to 1 |
rowsBetween() |
Frame based on row positions |
rangeBetween() |
Frame based on ORDER BY value range |
The core distinction is:
groupBy()
→ collapses rows
window function
→ keeps rows and adds group-relative calculations
What to look for
Latest row changes between runs
Your ordering is probably incomplete.
Add deterministic tie-break columns such as:
- event ID
- source sequence
- ingestion timestamp
last_value() equals the current row unexpectedly
Your window frame probably ends at the current row.
Use an explicit full-partition frame when you need the final value in the partition.
Running total jumps unexpectedly
Check:
- duplicate source rows
- ordering
- nulls
- whether ties create ambiguous sequencing
Window stage has one very slow task
Inspect partition-key skew.
One huge customer/tenant can dominate execution.
Window causes a massive sort/shuffle
Check:
- partition key cardinality
- whether filtering can happen earlier
- whether all columns are necessary
- whether the window can be narrowed
Rolling “7-day” metric is wrong
Check whether you used:
7 rows
instead of:
7 days
Use the correct frame type.
Production notes
Window functions are usually sort-heavy
Most ordered windows require Spark to:
partition
→ shuffle
→ sort
→ evaluate window
They can be expensive on large datasets.
Deterministic ordering matters
If the orderBy() values tie and no secondary key exists, row-level functions such as row_number(), lag(), and lead() may not have a stable business meaning.
Add a real tie-break rule.
Window partitions can become memory-heavy
Large partitions require significant sort/state handling.
Avoid partitioning millions of unrelated rows under one null or sentinel key.
Filtering before a window can change semantics
For example:
lifetime running total
requires full history.
If you filter to the last month first, you calculate:
month-to-date running total
not lifetime total.
Reduce input only when the business definition allows it.
rangeBetween() requires careful ordering semantics
The frame is based on the value of the order expression.
When using numeric epoch values, document the unit explicitly.
Use built-in window functions before custom Python logic
Built-ins stay in Spark’s optimized execution engine.
Do not collect ordered groups to Python to calculate rank, lag, or running totals.
Common mistakes
1. Using row_number() without a complete order
Tied rows can produce ambiguous winners.
2. Using rank() when exactly N rows are required
Ties can cause more than N rows to qualify.
Use row_number() for exactly N rows.
3. Using row_number() when all ties should survive
Use rank() or dense_rank() instead.
4. Treating first() / last() as time-aware automatically
Ordering and frame semantics must be explicit.
5. Confusing rowsBetween() with time ranges
A row frame counts rows, not elapsed time.
6. Using a global ordered window unnecessarily
This can force a very expensive global sort.
7. Ignoring partition skew
One hot key can dominate the entire window stage.
8. Filtering history away before calculating a lifetime window
This silently changes the metric definition.
9. Replacing all null keys with one sentinel
This can create one enormous window partition.
10. Using windows when groupBy() is enough
If you only need one row per group, aggregation may be simpler and cheaper.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.