How would you use a Spark window function with row_number() partitioned by customer and ordered by timestamp descending to get the latest order per customer?

Question

Assisting AI · Accepted Answer

In Spark you can use the Window API to assign a row number within each customer partition sorted by timestamp descending. The row with row_number = 1 is the most recent order. Example in Scala:

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

val w = Window.partitionBy("customer_id").orderBy(col("order_ts").desc)
val dfWithRank = df.withColumn("rn", row_number().over(w))
val latestPerCustomer = dfWithRank.filter(col("rn") === 1).drop("rn")
```

In PySpark the syntax is similar:

```python
from pyspark.sql.window import Window
from pyspark.sql.functions import row_number, col

w = Window.partitionBy("customer_id").orderBy(col("order_ts").desc())
df_with_rank = df.withColumn("rn", row_number().over(w))
latest = df_with_rank.filter(col("rn") == 1).drop("rn")
```

Complexity: The window operation requires a shuffle to group by customer_id, so it is O(n log n) in the number of rows. It scales well because Spark can parallelize the shuffle and the computation across executors. This approach avoids expensive self‑joins and is the idiomatic way to get the latest record per group in Spark.

How would you use a Spark window function with row_number() partitioned by customer and ordered by timestamp descending to get the latest order per customer?

💡 Model Answer

🎤 Get questions like this answered in real-time