Window functions: `partitionBy` and `orderBy`

Description

Every groupBy you've used in this guide — from module 3 through module 4 — did the same thing: it collapsed a group of rows into a single result row. fact_orders_df.groupBy("store_id").agg(F.sum("revenue")) turns forty orders into three rows, one per store; each individual order's detail disappears from the result. This lesson introduces a different tool, with a different shape: the window function, which computes an aggregated value — a sum, a ranking, whatever — for each row, using the group that row belongs to as context, without losing a single row from the original result. The syntax has two pieces: Window.partitionBy() (defines the group, just like groupBy) and .orderBy() (defines an order within that group, something groupBy doesn't have). This lesson verifies that syntax over Kiosko's forty real rows, checkable by hand, before lessons 6 and 7 apply it to two complete business questions.

Connection to the module. Lessons 2 through 4 closed this module's joins half. This lesson opens the second half from scratch: Window's basic syntax, without yet resolving any business question — that arrives in lessons 6 and 7, which build directly on what you learn here.

An analogy: the marathon runner, and their own cumulative time

Picture a marathon with checkpoints every few kilometers. At every checkpoint, each runner knows one specific fact about themselves: how long they've been running, from the start to that exact point. That cumulative time depends solely on their own run — the kilometers they, and only they, have already covered — with no need for any data from the other runners, nor any merging into a single group figure. And, at the same time, it's perfectly possible for a timing system to group runners by age category, and compute, for every checkpoint, each runner's position within their category — without that requiring the whole category to collapse into a single figure, or any runner to stop having their own individual time visible.

Window.partitionBy("age_category") is, precisely, the instruction "group runners by category" — the same idea as groupBy. .orderBy("cumulative_time") is the instruction "within each category, sort them by their time" — something groupBy, on its own, never had. And the central difference, the one that makes a window function a different tool and not just "a groupBy with extra steps": the result still has one row per runner, with their own time, their own position within their category — never a single row summarized per category.

Worked example: the syntax, over the forty real rows

Step 1 — fact_orders_df, as you already know it from module 3

# window_intro.py
import glob
from pyspark.sql import SparkSession, Window
from pyspark.sql import functions as F
from pyspark.sql.types import (
    StructType, StructField, StringType, IntegerType, DoubleType, TimestampType,
)

spark = SparkSession.builder.appName("kiosko-spark").master("local[*]").getOrCreate()

orders_schema = StructType([
    StructField("order_id", StringType(), False),
    StructField("store_id", StringType(), False),
    StructField("product_id", StringType(), False),
    StructField("quantity", IntegerType(), False),
    StructField("unit_price", DoubleType(), False),
    StructField("order_ts", TimestampType(), False),
])
orders_df = spark.read.csv(
    sorted(glob.glob("orders_2026-08-*.csv")), schema=orders_schema, header=True, enforceSchema=False,
)
fact_orders_df = orders_df.withColumn("revenue", F.col("quantity") * F.col("unit_price"))

Step 2 — The window specification: two pieces

store_window = Window.partitionBy("store_id").orderBy("order_ts")

This line, on its own, doesn't compute anything yet — just like a DataFrame built with .select()/.filter() in module 2, it's a specification, not an action. Window.partitionBy("store_id") says "group the rows by store, the way a groupBy("store_id") would." .orderBy("order_ts") says "within each store, sort the rows by their order date." None of this, yet, computes a single number — the third piece is still missing: which function to apply on top of that specification.

Step 3 — F.sum().over(), the piece that actually computes something

with_running = fact_orders_df.withColumn(
    "running_total", F.round(F.sum("revenue").over(store_window), 2)
)

print("=== S01, sorted by order_ts, with running_total ===")
with_running.filter(F.col("store_id") == "S01").orderBy("order_ts").select(
    "order_id", "store_id", "order_ts", F.round("revenue", 2).alias("revenue"), "running_total"
).show(20, truncate=False)

spark.stop()

What to expect. Running python3 window_intro.py, the output is exactly this (executed in this run, over Kiosko's forty real rows):

=== S01, sorted by order_ts, with running_total ===
+--------+--------+-------------------+-------+-------------+
|order_id|store_id|order_ts           |revenue|running_total|
+--------+--------+-------------------+-------+-------------+
|ORD-1001|S01     |2026-08-03 08:14:00|1.65   |1.65         |
|ORD-1002|S01     |2026-08-03 08:20:00|1.2    |2.85         |
|ORD-1004|S01     |2026-08-03 09:02:00|4.5    |7.35         |
|ORD-1008|S01     |2026-08-03 10:22:00|1.1    |8.45         |
|ORD-2001|S01     |2026-08-04 08:05:00|1.2    |9.65         |
|ORD-2004|S01     |2026-08-04 09:50:00|2.25   |11.9         |
|ORD-3002|S01     |2026-08-05 08:22:00|0.55   |12.45        |
|ORD-4001|S01     |2026-08-06 08:10:00|2.2    |14.65        |
|ORD-4004|S01     |2026-08-06 09:55:00|4.5    |19.15        |
|ORD-5001|S01     |2026-08-07 08:05:00|2.4    |21.55        |
|ORD-5004|S01     |2026-08-07 09:22:00|1.5    |23.05        |
|ORD-5007|S01     |2026-08-07 10:40:00|1.1    |24.15        |
|ORD-6001|S01     |2026-08-08 08:00:00|3.3    |27.45        |
|ORD-6004|S01     |2026-08-08 08:52:00|9.0    |36.45        |
|ORD-6007|S01     |2026-08-08 09:45:00|0.75   |37.2         |
|ORD-7001|S01     |2026-08-09 09:15:00|1.1    |38.3         |
+--------+--------+-------------------+-------+-------------+

Check this by hand, with the first few rows: ORD-1001 has revenue = 1.65 (3 × 0.55), and since it's S01's first order sorted by order_ts, its running_total is simply 1.65. ORD-1002 has revenue = 1.20, and its running_total is 1.65 + 1.20 = 2.85 — its own revenue plus the revenue of every row before it within the same store, sorted by date. And notice something central: the complete table has sixteen rows, one per real S01 order that week — no row disappeared, unlike what would have happened with a groupBy("store_id"), which would have produced a single row with the 38.3 total.

Diagram: groupBy versus Window, the same store, two differently shaped results

flowchart TD
    A["fact_orders_df filtered to S01\n16 rows"] --> B{"groupBy('store_id')\n.agg(F.sum('revenue'))"}
    A --> C{"Window.partitionBy('store_id')\n.orderBy('order_ts')\nwith F.sum('revenue').over()"}

    B --> D["1 row:\nstore_id=S01, sum=38.3\n(the 16 original rows\nno longer exist separately)"]
    C --> E["16 rows:\neach with its own\nrunning_total, the last = 38.3\n(all original rows\nstill present)"]

    style D fill:#f96,stroke:#333
    style E fill:#9c6,stroke:#333

Both paths arrive at the same final number — 38.3 — but in completely different ways: groupBy delivers it as the only result, losing all sixteen individual orders' granularity; Window delivers it as the last value of a new column, with the sixteen original rows still intact, each with its own running total up to that point.

Going deeper: .over(), and why the order of the pieces isn't arbitrary

It's worth naming, precisely, the three pieces that combine to produce running_total, because each one has a different role and none of them is optional:

1. The aggregate function (F.sum("revenue")): what calculation to perform. Any function you already know from groupBy — F.sum, F.avg, F.count, F.max, F.min — works here too, under the same name.
2. .over(window_spec): the bridge between the aggregate function and the window specification. Without .over(), F.sum("revenue") would still just be a normal aggregate function, the same one you'd use inside an .agg() after a groupBy — .over() is what turns it into a window function, computed row by row instead of collapsed per group.
3. The window specification (Window.partitionBy("store_id").orderBy("order_ts")): the context — which rows count as "the same group" (partitionBy), and in what order they get processed within that group (orderBy).

The window specification's orderBy has a consequence worth making explicit: by default, when you use F.sum(...).over(window) with an orderBy present, Spark doesn't sum the entire group — it sums only the rows from the start of the group up to the current row, in the order you defined. This is the exact reason running_total grows row by row instead of showing 38.3 on all sixteen of S01's rows — that cumulative behavior (technically, a frame running from unboundedPreceding to currentRow) is the default when there's an orderBy, and it's exactly what lesson 6 uses for the running revenue total. If you removed the orderBy from the specification, F.sum("revenue").over(Window.partitionBy("store_id")) would go back to summing the entire group on every row — 38.3 repeated sixteen times — because without an explicit order there's no notion of "up to here."

Common mistakes

Writing F.sum("revenue") without .over(), or with .over() misplaced. What happens: someone, used to .agg(F.sum("revenue")) after a groupBy, writes fact_orders_df.withColumn("total", F.sum("revenue")) with no .over(), expecting the same window behavior. Why it happens: the F.sum() function itself, by its name, doesn't distinguish whether it's going to be used as a groupBy aggregation or as a window function — the difference lies exclusively in whether .over() is chained onto it or not. How to spot it: if withColumn() with an aggregate function fails with an error about aggregation outside a valid context, or if your code doesn't compile because the window argument is missing, check that .over(window_spec) is present and correctly chained. How to fix it: any aggregate function you want to use as a window function needs an explicit .over(window_spec) — without that piece, Spark has no way of knowing which group or which order it should use.

Confusing Window's partitionBy with Parquet-write partitionBy. What happens: someone, seeing Window.partitionBy("store_id"), associates it with df.write.partitionBy("store_id") — a method you're going to see in module 7, for writing files organized into folders by column value — and assumes they have the same effect. Why it happens: both methods share the same name and, on the surface, the same idea of "grouping by a column." How to spot it: if you expect Window.partitionBy("store_id") to create folders on disk, or df.write.partitionBy("store_id") to compute a running total, you mixed up the two concepts. How to fix it: Window.partitionBy() is exclusively about how rows get grouped in memory, for computing a window function — it writes nothing to disk. df.write.partitionBy(), which you'll see in module 7, decides how output files get organized into folders. They share the name because both partition data by a column's value, but they operate at completely different layers of the system.

Expecting Window.partitionBy("store_id").orderBy("order_ts") to produce deterministic results when order_ts values repeat, without a second thought. What happens: someone assumes, without checking, that every row's running_total for a specific order_ts is always a single, predictable value, even when two or more rows share exactly the same order_ts within the same store. Why it happens: in Kiosko's forty real rows, every order_ts is unique within each store, so this case never shows up in this lesson's example — it's easy not to anticipate it. How to spot it: if a window's orderBy uses a single column that could have repeated values (like order_ts at scale, where thousands of franchises share the same real timestamp), and you can't reliably predict the exact order among those tied rows, you're missing a tiebreak criterion. How to fix it: when a window's orderBy could have ties, add one or more additional columns that are unique within the group (for example, order_id) to guarantee a fully deterministic order — lesson 6 runs into exactly this case when applying this same window over fact_orders_at_scale, and resolves it with real evidence.

Exercises

Exercise 1 — Confirm removing .orderBy() from the specification changes F.sum().over()'s behavior. Using fact_orders_df, compute a new store_total column with Window.partitionBy("store_id") (no .orderBy()), and confirm its value is the same — 38.3 for S01 — across that store's sixteen rows.

store_total_window = Window.partitionBy("store_id")
with_store_total = fact_orders_df.withColumn(
    "store_total", F.round(F.sum("revenue").over(store_total_window), 2)
)
with_store_total.filter(F.col("store_id") == "S01").orderBy("order_id").select(
    "order_id", "store_total"
).show(16, truncate=False)

distinct_values = with_store_total.filter(F.col("store_id") == "S01").select("store_total").distinct().count()
print(f"Distinct store_total values in S01: {distinct_values}")
assert distinct_values == 1
print("Verification: without orderBy, store_total is the same value repeated on every row -> OK")

+--------+-----------+
|order_id|store_total|
+--------+-----------+
|ORD-1001|38.3       |
|ORD-1002|38.3       |
|ORD-1004|38.3       |
|ORD-1008|38.3       |
|ORD-2001|38.3       |
|ORD-2004|38.3       |
|ORD-3002|38.3       |
|ORD-4001|38.3       |
|ORD-4004|38.3       |
|ORD-5001|38.3       |
|ORD-5004|38.3       |
|ORD-5007|38.3       |
|ORD-6001|38.3       |
|ORD-6004|38.3       |
|ORD-6007|38.3       |
|ORD-7001|38.3       |
+--------+-----------+

Distinct store_total values in S01: 1
Verification: without orderBy, store_total is the same value repeated on every row -> OK

Exercise 2 — Compute, on the same window, how many orders each S01 row has accumulated so far, with F.count(). Using store_window from the worked example (Window.partitionBy("store_id").orderBy("order_ts")), add an orders_so_far column with F.count("order_id").over(store_window), and confirm S01's last row shows 16.

with_count = fact_orders_df.withColumn("orders_so_far", F.count("order_id").over(store_window))
with_count.filter(F.col("store_id") == "S01").orderBy("order_ts").select(
    "order_id", "order_ts", "orders_so_far"
).show(16, truncate=False)

last_row = with_count.filter(F.col("store_id") == "S01").orderBy(F.desc("order_ts")).first()
assert last_row["orders_so_far"] == 16
print(f"orders_so_far on S01's last row = {last_row['orders_so_far']}")
print("Verification: F.count().over() also accumulates, just like F.sum() -> OK")

+--------+-------------------+--------------+
|order_id|order_ts           |orders_so_far |
+--------+-------------------+--------------+
|ORD-1001|2026-08-03 08:14:00|1             |
|...     |...                |...           |
|ORD-7001|2026-08-09 09:15:00|16            |
+--------+-------------------+--------------+

orders_so_far on S01's last row = 16
Verification: F.count().over() also accumulates, just like F.sum() -> OK

Exercise 3 — Explain, without code, what would happen if you used Window.partitionBy("order_id") instead of Window.partitionBy("store_id") for this same calculation. In 2-3 sentences, predict the result of F.sum("revenue").over(Window.partitionBy("order_id").orderBy("order_ts")) over fact_orders_df, knowing order_id is unique for every row.

Summary and next step

This lesson introduced a window function's complete syntax — Window.partitionBy().orderBy(), combined with an aggregate function via .over() — and verified, over Kiosko's forty real rows, the central difference from groupBy: no row disappears from the result. You confirmed running_total grows within each store following order_ts's order, and that without .orderBy(), the same specification produces a total repeated per group, instead of a running total.

Before moving on you should be able to: write a window's basic specification from memory (Window.partitionBy().orderBy()); explain what role each of the three pieces plays — aggregate function, .over(), window specification; and predict the behavior difference between having and not having .orderBy() in the specification.

Lesson 6 takes exactly this specification — Window.partitionBy("store_id").orderBy("order_ts") — and applies it to the complete business question: revenue accumulated per store, verified first over the forty real rows, and then over the complete fact_orders_at_scale.

Resources

• PySpark — pyspark.sql.Window (the complete reference for partitionBy, orderBy, rowsBetween, rangeBetween, and the window boundary constants). spark.apache.org/docs/latest/api/python/reference/pyspark.sql/window.html.
• This guide's DESIGN doc (spark-and-distributed-processing-guide/DISENO.md) — this lesson's exact specification: Window's syntax, verified over the forty real rows before applying it at scale.
• data-modeling-for-analytics-guide's DESIGN doc — the source of the running-total question this lesson begins resolving with Spark's native window, instead of array columns in DuckDB. src/guides/data-modeling-for-analytics-guide/DISENO.md