{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"running-totals-and-frames","title":"Running totals, and the frame you did not know you set","url":"https://addaly.com/learn/data-and-sql/running-totals-and-frames","position":37,"of":66,"minutes":9,"answer":"An ORDER BY inside a window sets a frame whose default, RANGE up to the current row, includes tied rows.","keyIdea":"An ORDER BY inside a window sets a frame whose default, RANGE up to the current row, includes tied rows; write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly when you mean one row at a time."},"body":{"format":"markdown","text":"## Adding ORDER BY to a window changes what it sums\n\n`sum(amount) OVER (PARTITION BY customer_id)` gives each row its customer's total. Add an ORDER BY inside the window and the same function becomes a running total:\n\n```sql\nSELECT customer_id, placed_at, amount,\n       sum(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS running_total\nFROM orders;\n```\n\nEach row now shows the sum of its customer's orders up to and including itself. The ORDER BY did not just sort; it introduced a **frame**, a rule about which of the ordered rows the aggregate covers. With no ORDER BY the frame is the whole partition. With an ORDER BY, the default frame is \"from the start of the partition up to the current row\", which is what makes it a running total.\n\n## The default is RANGE, and RANGE includes ties\n\nThe default frame is written out as `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`, and the word RANGE hides a surprise. In RANGE mode, \"current row\" means \"the current row and every row that ties with it in the ORDER BY\". Take four orders on days 1, 2, 2 and 3 with amounts 10, 20, 30 and 40:\n\n- `RANGE` (the default): running totals 10, 60, 60, 100. Both day-2 rows see each other, so both show 60.\n- `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`: 10, 30, 60, 100. Each row sees only the rows physically before it.\n\nWhich is right depends on the question. \"Cumulative revenue by the end of each day\" wants RANGE, because both rows on day 2 belong to a day whose total is 60. \"A running balance after each transaction\" wants ROWS, because a statement lists one line at a time. Most people want ROWS, get RANGE, and only notice on a day with two orders, when the running total appears to jump.\n\nSay the frame explicitly whenever the ORDER BY can tie. If you order by a unique key there are no ties and the two agree, which is another reason to end every window ORDER BY with the primary key.\n\n## Running totals that reset\n\nThe partition is the reset. `PARTITION BY customer_id` restarts at zero per customer; `PARTITION BY customer_id, date_trunc('year', placed_at)` restarts per customer per year, which is how \"year-to-date spend\" is built.\n\n## Cumulative share, and the 80/20 that is usually 80/15\n\nDivide a running total by the partition total and you have a cumulative share, which turns a sorted list into a curve:\n\n> Figure: Four orders, two of them on the same day, two running totals. Line chart of Running total (thousands) against Order, in date order (the second and third fall on the same day): RANGE, the default (1, 10) (2, 60) (3, 60) (4, 100); ROWS, written out (1, 10) (2, 30) (3, 60) (4, 100). The amounts are 10, 20, 30 and 40 thousand, on days 1, 2, 2 and 3. In the default RANGE frame the two day-2 rows tie in the ORDER BY, so each sees the other and both read 60. A ROWS frame counts one row at a time. Both are right for some question; only one is right for yours.\n\n```sql\nWITH c AS (\n  SELECT customer_id, revenue,\n         sum(revenue) OVER (ORDER BY revenue DESC, customer_id) AS running,\n         sum(revenue) OVER ()                                    AS total,\n         row_number() OVER (ORDER BY revenue DESC, customer_id)  AS rn,\n         count(*)     OVER ()                                    AS n\n  FROM customer_revenue\n)\nSELECT rn::numeric / n AS share_of_customers, running / total AS share_of_revenue\nFROM c;\n```\n\nFind the row where `share_of_revenue` first exceeds 0.8 and read `share_of_customers` from it. Marketing folklore says 20%; on real retail data the answer is often between 10% and 30%, and knowing your own number is worth more than the folklore. This is also the way to cut customers into groups of equal revenue rather than equal count.\n\n## Running counts and running distinct counts\n\n`count(*) OVER (ORDER BY placed_at)` is a running count and behaves exactly like the running sum. `count(DISTINCT customer_id) OVER (ORDER BY placed_at)` is not allowed in Postgres and most engines: distinct aggregates do not work as windows, because a running distinct count cannot be computed by adding one row's contribution to the previous total. The workaround is to number each customer's first order with `row_number()` partitioned by customer, keep the rows where it is 1, and take a running count of those. That is the \"customers acquired to date\" line on every growth chart.\n\n## The habit\n\nWhenever a window has an ORDER BY, write the frame out. `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` is eleven words that remove an entire category of bug, and a reader who does not know the default will thank you. The next two lessons use frames that look backwards a fixed distance rather than to the start, and the same rule applies there."},"check":{"question":"A running balance is computed with `sum(amount) OVER (ORDER BY txn_date)`. On days with two transactions, both rows show the same balance, which already includes both amounts. What is happening?","options":[{"text":"The two rows are duplicates produced by a fanning join upstream, so each carries the other's amount as well as its own.","correct":false,"tempting":"Duplicated rows would double the day's contribution to every later balance, not merely show the same value on both rows of the day."},{"text":"`sum()` as a window sums the whole partition when the ORDER BY column has repeated values, falling back to the no-ORDER-BY behaviour for those rows only.","correct":false,"tempting":"The window still accumulates from the start of the partition; only the rows tied at the current position are pulled in together."},{"text":"The transactions were inserted in the wrong order, so the second row's amount is being counted before the first.","correct":false,"tempting":"Insertion order is irrelevant to a window with an explicit ORDER BY; the issue is the frame's treatment of equal dates."},{"text":"The default RANGE frame treats rows tied on `txn_date` as one position, so each sees both amounts; a ROWS frame gives one line at a time.","correct":true}]},"prev":{"slug":"top-n-per-group","title":"Top N per group in two lines","url":"https://addaly.com/learn/data-and-sql/top-n-per-group"},"next":{"slug":"lag-lead-and-change","title":"LAG and LEAD: the previous row, and the change since it","url":"https://addaly.com/learn/data-and-sql/lag-lead-and-change"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Running totals, and the frame you did not know you set', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/running-totals-and-frames (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Running totals, and the frame you did not know you set","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":4,"slug":"windows-sequences-and-time","title":"Windows, sequences and time"},"answer":null,"keyIdea":"An ORDER BY inside a window sets a frame whose default, RANGE up to the current row, includes tied rows; write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explicitly when you mean one row at a time.","hero":{"kind":"figure","figure":{"t":"figure","label":"Figure","figure":{"type":"line","title":"Four orders, two of them on the same day, two running totals","series":[{"label":"RANGE, the default","points":[[1,10],[2,60],[3,60],[4,100]]},{"label":"ROWS, written out","points":[[1,10],[2,30],[3,60],[4,100]]}],"xLabel":"Order, in date order (the second and third fall on the same day)","yLabel":"Running total (thousands)","caption":"The amounts are 10, 20, 30 and 40 thousand, on days 1, 2, 2 and 3. In the default RANGE frame the two day-2 rows tie in the ORDER BY, so each sees the other and both read 60. A ROWS frame counts one row at a time. Both are right for some question; only one is right for yours."},"alt":"Figure: Four orders, two of them on the same day, two running totals. Line chart of Running total (thousands) against Order, in date order (the second and third fall on the same day): RANGE, the default (1, 10) (2, 60) (3, 60) (4, 100); ROWS, written out (1, 10) (2, 30) (3, 60) (4, 100). The amounts are 10, 20, 30 and 40 thousand, on days 1, 2, 2 and 3. In the default RANGE frame the two day-2 rows tie in the ORDER BY, so each sees the other and both read 60. A ROWS frame counts one row at a time. Both are right for some question; only one is right for yours."}},"blocks":[{"t":"h2","id":"adding-order-by-to-a-window-changes-what-it-sums","runs":[{"text":"Adding ORDER BY to a window changes what it sums"}]},{"t":"p","runs":[{"text":"sum(amount) OVER (PARTITION BY customer_id)","code":true},{"text":" gives each row its customer’s total. Add an ORDER BY inside the window and the same function becomes a running total:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT customer_id, placed_at, amount,\n       sum(amount) OVER (PARTITION BY customer_id ORDER BY placed_at) AS running_total\nFROM orders;"},{"t":"p","runs":[{"text":"Each row now shows the sum of its customer’s orders up to and including itself. The ORDER BY did not just sort; it introduced a "},{"text":"frame","b":true},{"text":", a rule about which of the ordered rows the aggregate covers. With no ORDER BY the frame is the whole partition. With an ORDER BY, the default frame is “from the start of the partition up to the current row”, which is what makes it a running total."}]},{"t":"h2","id":"the-default-is-range-and-range-includes-ties","runs":[{"text":"The default is RANGE, and RANGE includes ties"}]},{"t":"p","runs":[{"text":"The default frame is written out as "},{"text":"RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW","code":true},{"text":", and the word RANGE hides a surprise. In RANGE mode, “current row” means “the current row and every row that ties with it in the ORDER BY”. Take four orders on days 1, 2, 2 and 3 with amounts 10, 20, 30 and 40:"}]},{"t":"ul","items":[[{"text":"RANGE","code":true},{"text":" (the default): running totals 10, 60, 60, 100. Both day-2 rows see each other, so both show 60."}],[{"text":"ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW","code":true},{"text":": 10, 30, 60, 100. Each row sees only the rows physically before it."}]]},{"t":"p","runs":[{"text":"Which is right depends on the question. “Cumulative revenue by the end of each day” wants RANGE, because both rows on day 2 belong to a day whose total is 60. “A running balance after each transaction” wants ROWS, because a statement lists one line at a time. Most people want ROWS, get RANGE, and only notice on a day with two orders, when the running total appears to jump."}]},{"t":"p","runs":[{"text":"Say the frame explicitly whenever the ORDER BY can tie. If you order by a unique key there are no ties and the two agree, which is another reason to end every window ORDER BY with the primary key."}]},{"t":"h2","id":"running-totals-that-reset","runs":[{"text":"Running totals that reset"}]},{"t":"p","runs":[{"text":"The partition is the reset. "},{"text":"PARTITION BY customer_id","code":true},{"text":" restarts at zero per customer; "},{"text":"PARTITION BY customer_id, date_trunc('year', placed_at)","code":true},{"text":" restarts per customer per year, which is how “year-to-date spend” is built."}]},{"t":"h2","id":"cumulative-share-and-the-80-20-that-is-usually-80-15","runs":[{"text":"Cumulative share, and the 80/20 that is usually 80/15"}]},{"t":"p","runs":[{"text":"Divide a running total by the partition total and you have a cumulative share, which turns a sorted list into a curve:"}]},{"t":"code","lang":"sql","label":"SQL","text":"WITH c AS (\n  SELECT customer_id, revenue,\n         sum(revenue) OVER (ORDER BY revenue DESC, customer_id) AS running,\n         sum(revenue) OVER ()                                    AS total,\n         row_number() OVER (ORDER BY revenue DESC, customer_id)  AS rn,\n         count(*)     OVER ()                                    AS n\n  FROM customer_revenue\n)\nSELECT rn::numeric / n AS share_of_customers, running / total AS share_of_revenue\nFROM c;"},{"t":"p","runs":[{"text":"Find the row where "},{"text":"share_of_revenue","code":true},{"text":" first exceeds 0.8 and read "},{"text":"share_of_customers","code":true},{"text":" from it. Marketing folklore says 20%; on real retail data the answer is often between 10% and 30%, and knowing your own number is worth more than the folklore. This is also the way to cut customers into groups of equal revenue rather than equal count."}]},{"t":"h2","id":"running-counts-and-running-distinct-counts","runs":[{"text":"Running counts and running distinct counts"}]},{"t":"p","runs":[{"text":"count(*) OVER (ORDER BY placed_at)","code":true},{"text":" is a running count and behaves exactly like the running sum. "},{"text":"count(DISTINCT customer_id) OVER (ORDER BY placed_at)","code":true},{"text":" is not allowed in Postgres and most engines: distinct aggregates do not work as windows, because a running distinct count cannot be computed by adding one row’s contribution to the previous total. The workaround is to number each customer’s first order with "},{"text":"row_number()","code":true},{"text":" partitioned by customer, keep the rows where it is 1, and take a running count of those. That is the “customers acquired to date” line on every growth chart."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Whenever a window has an ORDER BY, write the frame out. "},{"text":"ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW","code":true},{"text":" is eleven words that remove an entire category of bug, and a reader who does not know the default will thank you. The next two lessons use frames that look backwards a fixed distance rather than to the start, and the same rule applies there."}]}],"check":{"question":[{"text":"A running balance is computed with "},{"text":"sum(amount) OVER (ORDER BY txn_date)","code":true},{"text":". On days with two transactions, both rows show the same balance, which already includes both amounts. What is happening?"}],"options":[{"text":[{"text":"The two rows are duplicates produced by a fanning join upstream, so each carries the other’s amount as well as its own."}],"correct":false,"tempting":[{"text":"Duplicated rows would double the day’s contribution to every later balance, not merely show the same value on both rows of the day."}]},{"text":[{"text":"sum()","code":true},{"text":" as a window sums the whole partition when the ORDER BY column has repeated values, falling back to the no-ORDER-BY behaviour for those rows only."}],"correct":false,"tempting":[{"text":"The window still accumulates from the start of the partition; only the rows tied at the current position are pulled in together."}]},{"text":[{"text":"The transactions were inserted in the wrong order, so the second row’s amount is being counted before the first."}],"correct":false,"tempting":[{"text":"Insertion order is irrelevant to a window with an explicit ORDER BY; the issue is the frame’s treatment of equal dates."}]},{"text":[{"text":"The default RANGE frame treats rows tied on "},{"text":"txn_date","code":true},{"text":" as one position, so each sees both amounts; a ROWS frame gives one line at a time."}],"correct":true,"tempting":null}],"order":[1,0,3,2],"why":null},"prev":{"slug":"top-n-per-group","title":"Top N per group in two lines","minutes":8},"next":{"slug":"lag-lead-and-change","title":"LAG and LEAD: the previous row, and the change since it","minutes":9}}}