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

  1. Partition rows by entity, order by time
  2. Take the current row
  3. Does a row exist n positions before (lag) or after (lead)? Yes: Return that row's value. No: Return the default, or null.
  4. Compute the difference, e.g. datediff or amount change
  5. 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 row

Example

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

Apache Spark topics

Spark Fundamentals

Spark Architecture

RDDs

DataFrames

Aggregations & Joins

Spark SQL

Window Functions

Cleaning & Complex Types

UDFs

Performance Tuning

Structured Streaming

MLlib & Ecosystem

Interview Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Master Apache Spark

PySpark and Spark SQL from the ground up: DataFrames, joins, window functions, partitions and performance tuning, Structured Streaming and more. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice PySpark Write real PySpark — DataFrames, joins, window functions and Spark SQL — in a live editor with hidden tests and instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Spark  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Try the same API on Practice PySpark Online right in your browser, or run it locally after pip install pyspark (needs Java 17+), or paste it into a Databricks Free Edition notebook.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic