Interview/Spark

Spark interview questions: Window functions: keep the row, add the answer

Interactive Spark interview questions on Window functions: keep the row, add the answer. Practice with the matching lesson. Rank, lag, and running totals keep every row. partitionBy is a shuffle — and forgetting it funnels everything to one task.

Lesson · Simulation

You need a per-customer running total but the report still needs every transaction row. Window or groupBy?

Answer it out loud, then reveal. Play steps through like the simulators.

Production scenario

Window functions: keep the row, add the answer

A 'rolling 30-day average' column turned a 6-minute job into a 90-minute job

Symptoms

  • One new metric column shipped last sprint
  • A single task never finishes while the rest of the cluster is idle
  • Runtime grows a little every day as history accumulates

All questions on this page

Indexed as FAQ. Open any item if you prefer a list to Play.

Showing 9 of 9 questions.

beginner

You need a per-customer running total but the report still needs every transaction row. Window or groupBy?

Window functions: keep the row, add the answer · tap to open the answer

Say this first: Window — it computes over related rows but returns a value per row, so nothing collapses.

Why: groupBy + agg reduces each key to one row; a window function keeps input cardinality and adds a column. In the plan you get a WindowExec with a Sort under it instead of a HashAggregate, and no join-back step to reattach the aggregate.

Common mistake: Doing groupBy then joining the aggregate back to the detail — two shuffles for what a window does in one.

Follow-up they will ask: How would you use a window to grab the latest row per key?

Lesson · Simulation

intermediate

rowsBetween(-2, 0) and rangeBetween(-2, 0) on the same ordered window return different numbers. Why?

Window functions: keep the row, add the answer · tap to open the answer

Say this first: rowsBetween counts physical rows; rangeBetween compares values of the ordering column.

Why: rowsBetween is positional — the two preceding rows regardless of their values. rangeBetween is a value frame: every row whose ordering value falls inside the offset is included, so gaps shrink the frame and ties pull in all peers. With duplicate ordering values, rangeBetween rows share identical frames.

Common mistake: Using rangeBetween on a timestamp and expecting exactly three rows in the frame.

Follow-up they will ask: Which one do you need for a true 7-day rolling sum over daily data with missing days?

Lesson · Simulation

senior

Your window job has one task running for 40 minutes while 199 finished in seconds. What did you write?

Window functions: keep the row, add the answer · tap to open the answer

Say this first: A Window.orderBy(...) with no partitionBy — every row was shuffled into a single partition.

Why: Without partitionBy, Spark must order the whole dataset globally, so it plans an Exchange SinglePartition beneath WindowExec and one task processes everything. The stage shows exactly 1 task carrying the entire Shuffle Read plus a large Spill (Disk) while the rest of the cluster idles.

Senior depth: Reduce first, then order: filter or pre-aggregate to candidates, or rank within real partitions and stitch them with a small per-partition offset table. A truly global ordering over billions of rows is a design problem, not a tuning knob.

Common mistake: Diagnosing it as skew and salting a key that the window does not even partition by.

Follow-up they will ask: How do you compute a global row number without a single-task stage?

Lesson · Simulation

architect

Dedup with row_number() = 1 versus dropDuplicates — which goes in the pipeline and why?

Window functions: keep the row, add the answer · tap to open the answer

Say this first: row_number() when you must keep a specific survivor; dropDuplicates when any row per key is acceptable.

Why: dropDuplicates(keys) dedups via a shuffle and aggregate and hands you an arbitrary row per key. partitionBy(keys).orderBy(updated_at.desc) with a filter on row_number() = 1 pays for a Sort inside WindowExec but deterministically keeps the newest record — which is exactly what CDC and SCD merges require.

Senior depth: Add a deterministic tiebreaker such as a sequence number or commit ordering. Without one, reruns pick different survivors and downstream diffs look like data loss.

Common mistake: Running dropDuplicates on a CDC feed and shipping a random version of each record.

Follow-up they will ask: What breaks if the ordering column has ties?

Lesson · Simulation

beginner

sum('amount').over(Window.partitionBy('region')) gives every row in a region the same number. Is that broken?

Window functions: keep the row, add the answer · tap to open the answer

Say this first: No — with no orderBy the frame is the entire partition, so every row correctly gets the partition total.

Remember these

  • No orderBy means the frame is the whole partition
  • Adding orderBy switches the default frame to a running total
  • sum().over() with no orderBy is legal and silently returns a total
  • row_number() does throw without orderBy, which builds a false expectation

Why: A window has three parts and only partitionBy was supplied. Without an orderBy there is no notion of position, so the frame defaults to the whole partition and each row's sum covers every row with the same region — the partition total, broadcast onto every row. Add orderBy and the default frame changes to unbounded preceding through current row, which is what turns the same expression into a running total. This is the most dangerous window mistake precisely because it does not raise an error: the query succeeds and the numbers look plausible. row_number() behaves differently, it refuses to plan without an orderBy, which is why people assume sum() would too.

w_total = Window.partitionBy("region")
w_running = Window.partitionBy("region").orderBy("ts")
df.withColumn("region_total", F.sum("amount").over(w_total)) \
  .withColumn("running", F.sum("amount").over(w_running))

Common mistake: Treating the repeated value as a bug and adding a distinct or a groupBy to 'fix' it.

Follow-up they will ask: Where is a partition total on every row actually the thing you want?

Lesson · Simulation

intermediate

lag(amount, 1) returns null for the first row of every partition, and you also need the previous non-null value rather than the previous row. How do you handle both?

Window functions: keep the row, add the answer · tap to open the answer

Say this first: Pass a default to lag for the boundary, and use last(col, ignorenulls=True) over a frame ending before the current row for the previous non-null.

Remember these

  • lag never crosses a partition boundary, so a boundary null is correct
  • lag's third argument supplies a default value in place of the null
  • Previous non-null needs last(..., ignorenulls=True) over a trailing frame
  • Multiple functions over the same WindowSpec share one Exchange and Sort

Why: The null at the partition boundary is correct behaviour: there is no previous row inside that partition, and lag never crosses a partitionBy boundary. lag takes a third argument that substitutes a default, so lag('amount', 1, 0) gives zero instead of null where you want a numeric baseline rather than a gap. 'Previous non-null' is a different question — it is not a fixed offset, so lag cannot express it. The idiom is last with ignorenulls over a frame of unboundedPreceding to -1, which scans backwards through the ordered partition and returns the most recent value that was not null. Both operators require an orderBy, and both pay the same Exchange and Sort beneath WindowExec, so computing them together over one WindowSpec costs no more than computing one.

w = Window.partitionBy("device_id").orderBy("ts")
df.withColumn("prev", F.lag("reading", 1, 0.0).over(w)) \
  .withColumn("last_known", F.last("reading", ignorenulls=True).over(w.rowsBetween(Window.unboundedPreceding, -1)))

Common mistake: Filling boundary nulls with a coalesce to zero when the business meaning is 'no prior reading'.

Follow-up they will ask: What does last(..., ignoreNulls=True) do if the entire preceding frame is null?

Lesson · Simulation

advanced

One select has three window functions over three different WindowSpecs. How many shuffles does that plan contain?

Window functions: keep the row, add the answer · tap to open the answer

Say this first: One Exchange per distinct partitioning — functions sharing a partitionBy collapse into one WindowExec, different partitionBy columns each force their own.

Remember these

  • Windows with identical specs are fused into one WindowExec
  • Same partitionBy, different orderBy shares the Exchange but adds a Sort
  • A different partitionBy column means a whole new shuffle
  • Count Exchange nodes under Window operators in explain('formatted')

Why: The planner groups window expressions by their window specification. Functions with the same partitionBy and the same orderBy become a single WindowExec over one Exchange hashpartitioning and one Sort. Functions that share the partitionBy but differ in orderBy still share the Exchange, since the distribution requirement is identical, but each needs its own Sort and its own Window operator stacked on top. Functions partitioned by a different column cannot reuse any of it: the planner inserts another Exchange, another Sort, and another Window, and you now pay a second full shuffle of the dataset. So three specs over three different keys is three shuffles, which is why adding one 'small extra metric' partitioned by a different column can double a job's runtime. Read it off explain('formatted') by counting Exchange nodes beneath the Window operators.

w1 = Window.partitionBy("customer_id").orderBy("ts")
w2 = Window.partitionBy("customer_id").orderBy(F.desc("amount"))  # same Exchange
w3 = Window.partitionBy("region").orderBy("ts")                    # second Exchange

Senior depth: Compute them in separate passes at their natural grain and join the small result back, rather than forcing both windows over the full detail. The region metric is usually a per-region aggregate, which is a groupBy producing a tiny frame you can broadcast onto the detail. Paying a broadcast join instead of a full second shuffle of the fact table is nearly always the cheaper plan.

Common mistake: Assuming all window functions in one select share a single shuffle regardless of their keys.

Follow-up they will ask: You need metrics at customer grain and region grain in one output. How do you avoid the second shuffle?

Lesson · Simulation

advanced

Someone used row_number() to take the top 3 products per category and the results are unstable across runs. Walk through rank, dense_rank, and row_number.

Window functions: keep the row, add the answer · tap to open the answer

Say this first: row_number breaks ties arbitrarily, rank gives ties the same number and skips the next, dense_rank gives ties the same number and does not skip.

Remember these

  • row_number is 1,2,3; rank is 1,1,3; dense_rank is 1,1,2
  • row_number with ties needs a deterministic tiebreaker or it is unstable
  • Top-N including ties means rank or dense_rank, not row_number
  • The rank filter sits above the window, though newer Spark can push a group limit down

Why: With sales of 100, 100, 90, row_number assigns 1, 2, 3 — the two hundreds are separated in whatever order the sort happened to produce, which is not stable across runs because the sort is not total. rank assigns 1, 1, 3, preserving the tie and skipping position 2. dense_rank assigns 1, 1, 2. For 'top 3 including ties' you want rank() <= 3 or dense_rank() <= 3 depending on whether skipped positions should consume slots; for exactly three rows you want row_number, but then you must add a deterministic tiebreaker column to the orderBy or accept nondeterminism. Note also that the filter on the rank is applied above WindowExec, so conceptually the whole partition is still ordered — though recent Spark versions can push a per-group limit beneath the window for rank-style functions, which you may see as a WindowGroupLimit node in the plan.

w = Window.partitionBy("category").orderBy(F.desc("sales"), F.asc("product_id"))
top3 = df.withColumn("rn", F.row_number().over(w)).where("rn <= 3").drop("rn")

Senior depth: Parallelism collapses to eight. partitionBy is the shuffle key, so eight distinct categories means eight non-empty post-shuffle partitions and eight tasks, each holding an eighth of a billion rows to sort. The Stage page shows a handful of enormous tasks with heavy spill. Pre-aggregate to the grain the ranking actually needs — product totals per category — before ranking, so the window runs over thousands of rows instead of a billion.

Common mistake: Using row_number for 'top N' and silently dropping legitimate ties every run.

Follow-up they will ask: The category column has only eight distinct values on a billion-row table. What goes wrong?

Lesson · Simulation

senior

Your window stage spills hundreds of GB, but the partitionBy key is evenly distributed. What else causes that?

Window functions: keep the row, add the answer · tap to open the answer

Say this first: WindowExec buffers rows of the current frame in memory, so wide rows and unbounded frames spill even without skew.

Remember these

  • WindowExec buffers whole rows of the active frame, not just referenced columns
  • Unbounded frames make the buffer grow with the partition
  • windowExec.buffer.in.memory.threshold controls when spilling starts
  • Narrow the projection before the window instead of raising thresholds

Why: After the Exchange and Sort, WindowExec walks the sorted partition and keeps the rows of the active frame buffered so the aggregate can be evaluated. With a frame of unbounded preceding to current row, that buffer grows with the partition, and every column of every buffered row is held — not just the ones the window function references. So a partition of a few million wide rows will spill even when the key distribution is perfectly flat. Two configs control when it gives up on memory: spark.sql.windowExec.buffer.in.memory.threshold sets how many rows are held before spilling begins, and spark.sql.windowExec.buffer.spill.threshold bounds it further. The better fix is to shrink the rows rather than raise the thresholds: select only the columns the window needs, compute the window, then join the result back onto the detail by key if you need the wide columns in the output.

keys = df.select("customer_id", "ts", "amount")
ranked = keys.withColumn("running", F.sum("amount").over(w))
out = df.join(ranked.select("customer_id", "ts", "running"), ["customer_id", "ts"])

Senior depth: Yes, materially. A bounded frame means the buffer holds at most the frame width, so memory per partition becomes roughly constant instead of growing with partition size. If the metric is genuinely a trailing seven-row or seven-day window, say so explicitly rather than relying on the default running-total frame — it is both more correct and far cheaper.

Common mistake: Raising executor memory when the row width going into the window is what needs to change.

Follow-up they will ask: Would a bounded frame like rowsBetween(-6, 0) change the spill behaviour?

Lesson · Simulation