Previous and next rows

Read neighboring rows and calculate differences with explicit boundary handling.

Step 1 of 6 · Learn

Look in either direction

lag reads an earlier row; lead reads a later row in the window order. Here order_id defines the sequence, not a date or elapsed time. An offset of 1 means one row away. At a boundary with no neighbor, Spark returns null. Partitioning would restart this sequence for each group.

Lesson reference: PySpark and Scala

Look in either direction

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import lag

result = orders.withColumn(
    "neighbor_total", lag("total", 1).over(Window.orderBy("order_id"))
).select("order_id", "total", "neighbor_total").orderBy("order_id")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val result = orders.withColumn(
    "neighbor_total", lag("total", 1).over(Window.orderBy("order_id"))
).select("order_id", "total", "neighbor_total").orderBy("order_id")
result.show(false)

Change the offset

The offset is a row count within the ordered partition. lag(total, 1) reads one row back; increasing the offset looks farther back without changing the current row. Skipped order IDs do not create extra rows. The first rows return null when the requested neighbor does not exist.

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import lag

result = orders.withColumn(
    "neighbor_total", lag("total", 1).over(Window.orderBy("order_id"))
).select("order_id", "total", "neighbor_total").orderBy("order_id")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val result = orders.withColumn(
    "neighbor_total", lag("total", 1).over(Window.orderBy("order_id"))
).select("order_id", "total", "neighbor_total").orderBy("order_id")
result.show(false)

Calculate a difference

Subtracting the previous total gives the change between adjacent rows. The first change is null because subtraction with null returns null. Treating a missing previous value as zero is a separate business choice: it makes the first change equal to its current total, not zero.

PySpark example

from pyspark.sql import Window
from pyspark.sql.functions import col, lag

result = orders.withColumn(
    "previous_total", lag("total", 1).over(Window.orderBy("order_id"))
).withColumn(
    "change", col("total") - col("previous_total")
).select("order_id", "total", "change").orderBy("order_id")
result.show(truncate=False)

Scala example

import org.apache.spark.sql.expressions.Window
import org.apache.spark.sql.functions._

val result = orders.withColumn(
    "previous_total", lag("total", 1).over(Window.orderBy("order_id"))
).withColumn(
    "change", col("total") - col("previous_total")
).select("order_id", "total", "change").orderBy("order_id")
result.show(false)

example.pyPySpark

from pyspark.sql import Window from pyspark.sql.functions import lag result = orders.withColumn( "neighbor_total", lag("total", 1).over(Window.orderBy("order_id")) ).select("order_id", "total", "neighbor_total").orderBy("order_id") result.show(truncate=False)

Ordered rowsRead a neighboring valueKeep the current row

Window sequenceOrder IDs in window order1001 → 1002 → 1003 → 1004 → 1005 → 1006

Source orders6 rows

order_id	customer_id	status	total	item_count
1001	101	Delivered	89.5	2
1002	102	Shipped	149	3
1003	101	Cancelled	35	1
1004	103	Delivered	219.99	4
1005	104	Delivered	49.99	1
1006	105	Shipped	120	2

Look in either direction

Result6 rows

order_id	total	neighbor_total
1001	89.5	NULL
1002	149	89.5
1003	35	149
1004	219.99	35
1005	49.99	219.99
1006	120	49.99

Order 1001 has no previous row. Order 1002 reads 89.50 from order 1001.

The example is loaded in the editor. Run it as written, then try a small change.

Runs on the Spark backend. First startup may take a moment.

Run your code to see the result.