What a Window Frame Actually Is
Concepts covered: Window Frames, Aggregate Window Fns
The frame is the set of rows the SUM adds together to produce the value on 1 output row, and the engine assembles a fresh one for every row it computes. On the March 7 row the frame holds 3 rows, March 7 and the 2 dated before it, so the value is 500 minus 120 plus 250, which is 630. On the March 10 row the frame has grown by 1 row and the value is 550. Nothing else about the query changes from row to row: the column and the sort stay fixed while the frame gets 1 row longer each time. A candidate who can list the rows in the frame for any row the interviewer points at has understood running totals, and the rest is spelling.
| Current row | Rows in the frame | Amounts added | running_balance |
|---|---|---|---|
| 2024-03-01 | March 1 | 500 | 500 |
| 2024-03-03 | March 1, 3 | 500 - 120 | 380 |
| 2024-03-07 | March 1, 3, 7 | 500 - 120 + 250 | 630 |
| 2024-03-10 | March 1, 3, 7, 10 | 500 - 120 + 250 - 80 | 550 |
| 2024-03-14 | March 1, 3, 7, 10, 14 | 500 - 120 + 250 - 80 - 300 | 250 |
Reading the frame clause word by word
PRECEDING and FOLLOWING measure from the current row outward, and UNBOUNDED means as far as the sorted data reaches in that direction. A frame of ROWS BETWEEN 1 PRECEDING AND CURRENT ROW would hold only the current row and the one before it. That column is a 2-row moving sum, 380 on March 3 and then 130 on March 7, which answers a different question. For a balance after each transaction the start edge sits at the first row no matter how far down the table you are, so UNBOUNDED PRECEDING is the only correct start.
3 ways to write the window, 3 results
| OVER clause | Frame on the March 7 row | Value on March 7 |
|---|---|---|
| OVER () | all 5 rows | 250, the grand total |
| OVER (ORDER BY txn_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) | March 1, March 3, March 7 | 630, the total so far |
| OVER (ORDER BY txn_date) | default frame: every row whose date is at or before March 7 | 630 here, because no 2 rows share a date |
When ORDER BY is present and no frame is written, the SQL standard supplies one: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE gathers rows by the value of the ordering column, so every row carrying the current row's date is inside the frame at once. On this statement no 2 rows share a date, so the RANGE column and the ROWS column read identically, 500 through 250. The day the table gains 2 transactions on 1 date they disagree, and that disagreement gets a section of its own in the intermediate lesson. Writing ROWS out stops tied rows sharing 1 value, but it does not decide which tied row comes first, so end the ordering on a unique column: ORDER BY txn_date, txn_id. Saying "ROWS, written explicitly, with txn_id as the tiebreaker" tells the interviewer you know the default exists and that a sort on dates alone is not unique.
The frame questions the interviewer asks
"Which rows does the sum see on the fourth row?" Answer with the rows and the arithmetic: all 4 from March 1 through March 10, so 500 minus 120 plus 250 minus 80, which is 550. "Why UNBOUNDED?" Because the balance after any transaction depends on every earlier one back to the account's opening, so the frame can never start later than row 1. "Why end at CURRENT ROW?" Because the question says after, and after includes the transaction itself; an end edge of 1 PRECEDING would give the balance before each transaction, NULL on the first row since its frame is empty, then 500, 380, 630, 550, which is a legitimate column and a different question. Every one of these answers names specific rows.
The engine sorts the rows once, then walks them holding an accumulator and adds each new row into it as the frame's end edge advances, so a running total over 1 million rows costs 1 sort and 1 pass. The frame's start edge never moves, which is what makes that single pass possible; a frame whose start edge slides needs the engine to subtract rows as well, a cost the intermediate lesson's rolling windows pay. The self-join of the previous section examines every pair instead, which is why the window scales and the join does not.
About this lesson
This section is part of the Running Totals: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.