26. Analytic functions: `lag`, `lead`, `first`, `last`

These read other rows relative to the current one — the previous order, the next event, the first value in the group. All need an ordered window.

lag and lead — look back and ahead

lag(col, n) pulls the value from n rows before the current row (default 1); lead(col, n) looks n rows ahead. Within each customer, ordered by date:

w = Window.partitionBy("customer_id").orderBy("order_date")

orders.dropDuplicates().select(
    "customer_id", "order_date", "product",
    F.lag("product").over(w).alias("prev_product"),
    F.lead("product").over(w).alias("next_product"),
).show(10)

The first row of each partition has no previous row, so lag is null there (and lead is null on the last row). Pass a default to fill it: F.lag("product", 1, "—").

The killer use: differences between rows

lag is how you compute change over time — the gap between consecutive orders, day-over-day deltas, growth:

w = Window.partitionBy("customer_id").orderBy("order_date")

orders.dropDuplicates().withColumn(
    "days_since_prev",
    F.datediff("order_date", F.lag("order_date").over(w)),
).select("customer_id", "order_date", "product", "days_since_prev").show(10)

first and last

first(col) / last(col) return the first/last value in the window frame — e.g. each customer's first-ever product:

w = Window.partitionBy("customer_id").orderBy("order_date")
orders.dropDuplicates().withColumn(
    "first_product", F.first("product").over(w)
).select("customer_id", "order_date", "product", "first_product").show(8)

last needs a full frame. By default an ordered window only sees rows up to the current one, so last returns the current row, not the partition's true last. To get the real last value, widen the frame to the whole partition (next lesson covers frames).

Your turn

For each customer's orders (ordered by order_date), add the previous order's product as prev_product using lag. Return customer_id, order_date, product, prev_product, from deduplicated orders. Assign to result.

w = Window.partitionBy("customer_id").orderBy("order_date")
result = orders  # <- dropDuplicates, add lag("product").over(w) AS prev_product, select 4 cols
result.show()

w = Window.partitionBy("customer_id").orderBy("order_date")
result = orders.dropDuplicates().select(
    "customer_id", "order_date", "product",
    F.lag("product").over(w).alias("prev_product"),
)