lag & lead
Apache Spark · Window Functions
Theory
lag(col, n) returns the value of a column from the row n positions before the current row in the window order, and lead(col, n) returns the value from n rows after. n defaults to 1, and you can pass a default value to use instead of null when no such row exists (for example the first order of a customer has no previous order). Both need a window with orderBy, usually partitioned by an entity such as customer_id.
For example, with orders partitioned by customer_id and ordered by order_date, lag("order_date") gives each order the date of the same customer's previous order, and F.datediff("order_date", "prev_date") gives the days between purchases. lead("amount") lets you compare an order with the next one. These are the standard tools for change-over-time questions: growth, gaps, sessions and streaks.
Why we use it
Comparing a row with its neighbour without lag would need a self-join on a row number, which is slow and hard to read. lag and lead do it in one sorted pass, making day-over-day change, time between events and previous-status checks easy.
When to use it
Use lag/lead for previous/next comparisons inside an ordered sequence: month-over-month growth, time since last login, status changes. For cumulative values over many rows use a frame with sum/avg instead. Always partition by the entity, or values leak across customers.
How it works
- Partition rows by entity, order by time
- Take the current row
- Does a row exist n positions before (lag) or after (lead)? Yes: Return that row's value. No: Return the default, or null.
- Compute the difference, e.g. datediff or amount change
- Each row now carries its neighbour's value
Syntax
w = Window.partitionBy("customer_id").orderBy("order_date")
F.lag("amount").over(w) # previous row, null if none
F.lag("amount", 1, 0).over(w) # default 0 instead of null
F.lead("order_date").over(w) # next rowExample
from pyspark.sql import SparkSession, functions as F
from pyspark.sql.window import Window
spark = SparkSession.builder.appName("techtown-spark").getOrCreate()
orders = spark.createDataFrame([
(1, 101, "North", "Laptop", 1, 55000.0, "2026-01-05"),
(2, 102, "West", "Phone", 2, 30000.0, "2026-01-06"),
(3, 101, "North", "Mouse", 3, 1500.0, "2026-01-07"),
(4, 103, "South", "Laptop", 1, 52000.0, "2026-01-07"),
(5, 102, "West", "Tablet", 1, 25000.0, "2026-01-09"),
(6, 104, "North", "Phone", 1, 18000.0, "2026-01-10"),
(7, 103, "South", "Mouse", 2, 1000.0, "2026-01-11"),
(8, 105, "West", "Laptop", 1, 60000.0, "2026-01-12"),
], "order_id INT, customer_id INT, region STRING, product STRING, qty INT, amount DOUBLE, order_date STRING"
).withColumn("order_date", F.to_date("order_date"))
w = Window.partitionBy("customer_id").orderBy("order_date", "order_id")
history = (orders
.withColumn("prev_date", F.lag("order_date").over(w))
.withColumn("days_since_prev", F.datediff("order_date", "prev_date"))
.withColumn("prev_amount", F.lag("amount", 1, 0.0).over(w))
.withColumn("next_product", F.lead("product").over(w))
.select("customer_id", "order_id", "order_date", "prev_date",
"days_since_prev", "prev_amount", "next_product")
.orderBy("customer_id", "order_date"))
history.show()
# Output:
# +-----------+--------+----------+----------+---------------+-----------+------------+
# |customer_id|order_id|order_date| prev_date|days_since_prev|prev_amount|next_product|
# +-----------+--------+----------+----------+---------------+-----------+------------+
# | 101| 1|2026-01-05| NULL| NULL| 0.0| Mouse|
# | 101| 3|2026-01-07|2026-01-05| 2| 55000.0| NULL|
# | 102| 2|2026-01-06| NULL| NULL| 0.0| Tablet|
# | 102| 5|2026-01-09|2026-01-06| 3| 30000.0| NULL|
# | 103| 4|2026-01-07| NULL| NULL| 0.0| Mouse|
# | 103| 7|2026-01-11|2026-01-07| 4| 52000.0| NULL|
# | 104| 6|2026-01-10| NULL| NULL| 0.0| NULL|
# | 105| 8|2026-01-12| NULL| NULL| 0.0| NULL|
# +-----------+--------+----------+----------+---------------+-----------+------------+
Key points
- lag looks backward, lead looks forward, both by n rows (default 1)
- The first row of each partition gets null from lag unless you pass a default
- Always partitionBy the entity (customer, user) so values do not cross entities
- Combine with datediff or subtraction to get gaps and changes
- lag/lead ignore frames: they always count rows from the current row