BrevFeed
Sign in

28. Practical window patterns

📖 Reading · 4 min
💡 Every code box below is live — edit it and hit Run.

Three patterns you'll use constantly — the reason window functions are worth mastering.

1. Deduplicate — keep one row per key

dropDuplicates keeps an arbitrary row per key. When you want a specific one (the latest, the cheapest), use row_number and keep rank 1:

# keep only the FIRST row for each order_id (kills the duplicate 1021)
w = Window.partitionBy("order_id").orderBy("order_date")
deduped = orders.withColumn("rn", F.row_number().over(w)).filter(F.col("rn") == 1).drop("rn")
print("41 ->", deduped.count())

Change the orderBy and you control which duplicate survives — order by a timestamp desc() to keep the newest.

2. Top-N per group

row_number per partition, filter to <= N. The top 2 priciest products in every category:

w = Window.partitionBy("category").orderBy(F.col("unit_price").desc())
orders.dropDuplicates().withColumn("rn", F.row_number().over(w)).filter(
    F.col("rn") <= 2
).select("category", "product", "unit_price").show(12)

This is the answer to "top N per group" — a groupBy can't do it (it collapses the rows you want to keep).

3. Sessionization / streaks

Flag where a new "session" starts (a gap bigger than some threshold), then cumulative-sum the flags into a session id. Here: a customer's orders more than 3 days apart start a new streak.

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

sessions = (
    orders.dropDuplicates()
    .withColumn("gap", F.datediff("order_date", F.lag("order_date").over(w)))
    .withColumn("is_new", F.when((F.col("gap") > 3) | F.col("gap").isNull(), 1).otherwise(0))
    .withColumn("session", F.sum("is_new").over(w))
)
sessions.select("customer_id", "order_date", "gap", "session").show(12)

The lag → flag → cumulative sum trio is the general sessionization recipe — it works for clickstreams, logins, any event stream.

Your turn

Get the single most expensive product per category: rank by unit_price descending within category with row_number, keep rank 1, and return category, product, unit_price, from deduplicated orders. Assign to result.

w = Window.partitionBy("category").orderBy(F.col("unit_price").desc())
result = orders  # <- dropDuplicates, row_number().over(w) AS rn, keep rn == 1, select 3 cols
result.show()

w = Window.partitionBy("category").orderBy(F.col("unit_price").desc())
result = (
    orders.dropDuplicates()
    .withColumn("rn", F.row_number().over(w))
    .filter(F.col("rn") == 1)
    .select("category", "product", "unit_price")
)
← Previous Next →