Browse cheat sheets · PySpark

Technology: PySparkCategory: Data TransformationDifficulty: IntermediateUpdated

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
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Import Window and Spark functions
  5. 2. Assign a row number within each group
  6. 3. Reproduce SQL ROW_NUMBER()
  7. 4. Keep the latest row per business key
  8. 5. Keep top-N rows per group
  9. 6. Use rank() when ties share the same rank
  10. 7. Use dense_rank() when ties should not create gaps
  11. 8. Compare row_number, rank, and dense_rank
  12. 9. Get the previous row value with lag()
  13. 10. Get the next row value with lead()
  14. 11. Calculate time since previous event
  15. 12. Detect value changes
  16. 13. Build a running total
  17. 14. Build a running count
  18. 15. Build a running maximum
  19. 16. Use a rolling row-count window
  20. 17. Use rangeBetween() for value-based frames
  21. 18. Understand rowsBetween() vs rangeBetween()
  22. 19. Calculate partition total without reducing rows
  23. 20. Calculate percentage of group total
  24. 21. Calculate partition average
  25. 22. Compare row value to group average
  26. 23. Use first_value() with an ordered window
  27. 24. Use last_value() with an explicit full-partition frame
  28. 25. Get the first non-null value
  29. 26. Use nth_value() where supported and appropriate
  30. 27. Use cume_dist() for cumulative distribution
  31. 28. Use percent_rank() for relative ranking
  32. 29. Use ntile() for bucket assignment
  33. 30. Build effective-date ranges with lead()
  34. 31. Create session boundaries from event gaps
  35. 32. Assign session IDs with a cumulative sum
  36. 33. Deduplicate with row_number() before a MERGE
  37. 34. Avoid global windows unless they are truly required
  38. 35. Keep partitions aligned with the business entity
  39. 36. Check partition skew before large windows
  40. 37. Do not replace null partition keys with one global sentinel casually
  41. 38. Reuse the same window specification
  42. 39. Inspect the physical plan
  43. 40. Filter before a large window when safe
  44. What the result means
  45. What to look for
  46. Latest row changes between runs
  47. last_value() equals the current row unexpectedly
  48. Running total jumps unexpectedly
  49. Window stage has one very slow task
  50. Window causes a massive sort/shuffle
  51. Rolling “7-day” metric is wrong
  52. Production notes
  53. Window functions are usually sort-heavy
  54. Deterministic ordering matters
  55. Window partitions can become memory-heavy
  56. Filtering before a window can change semantics
  57. rangeBetween() requires careful ordering semantics
  58. Use built-in window functions before custom Python logic
  59. Common mistakes
  60. 1. Using row_number() without a complete order
  61. 2. Using rank() when exactly N rows are required
  62. 3. Using row_number() when all ties should survive
  63. 4. Treating first() / last() as time-aware automatically
  64. 5. Confusing rowsBetween() with time ranges
  65. 6. Using a global ordered window unnecessarily
  66. 7. Ignoring partition skew
  67. 8. Filtering history away before calculating a lifetime window
  68. 9. Replacing all null keys with one sentinel
  69. 10. Using windows when groupBy() is enough
  70. Next step
  71. 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:

  • Window
  • Exchange
  • Sort

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.

Next step

Use the requirement to choose the next action.

  • Need latest row per key → use row_number() with deterministic descending order.
  • Need top-N exactly → use row_number().
  • Need all ties → use rank() or dense_rank().
  • Need previous/next event → use lag() / lead().
  • Need running total → use an ordered cumulative frame.
  • Need rolling time window → use a range-based frame with explicit time units.
  • Need one scalar row per group → use groupBy() instead of a window.
  • Window is slow → inspect partition skew, sort/shuffle, and input size.
  • Latest-row logic feeds Delta MERGE → deduplicate deterministically before writing.
  • Window remains expensive after pruning → move to PySpark Performance Quick Reference and inspect the Spark plan/stages.

← All cheat sheets