SQL Interview Prep · Lesson

Cumulative Sums With Window Frames

Building a running total using SUM OVER with an ordered frame.

Cumulative Sums With Window Frames is a free SQL Interview Prep lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Running Total Question

Almost every analyst interview includes some form of: "Show me the cumulative revenue over time." A running total is a sum that grows row by row, accumulating everything from the start up to the current row.

Before window functions existed, candidates solved this with a slow self-join or a correlated subquery. The modern, expected answer is SUM(...) OVER (ORDER BY ...). Knowing the window-frame version signals you understand SQL written after about 2012.

Anatomy of an Ordered Window Sum

A running total is just an aggregate turned into a window function. You keep SUM(amount) but add an OVER clause with an ORDER BY.

The ORDER BY inside OVER is what makes it cumulative: it tells SQL to accumulate rows in that sequence. Without an ORDER BY, SUM would total the whole partition for every row instead of growing.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales
ORDER BY sale_date;

Why ORDER BY Implies a Frame

Here is the detail interviewers love to probe: when you add ORDER BY to a window aggregate, SQL applies a default frame of RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

That default is exactly what produces a running total: every row from the start of the partition up to and including the current row. If you understand this default, you understand why the cumulative sum "just works."

Making the Frame Explicit

You can write the frame out by hand. These two queries return the same result, but the explicit version shows the interviewer you know what is happening under the hood.

Writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the safest explicit form for a running total because it counts physical rows, avoiding the value-grouping surprises of RANGE (covered in the next lesson).

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Worked Example: Daily Sales

Imagine four days of sales: Mon 100, Tue 50, Wed 200, Thu 75. The running total accumulates left to right.

Mon: 100
Tue: 100 + 50 = 150
Wed: 150 + 200 = 350
Thu: 350 + 75 = 425

The final row always equals the grand total. That is a quick sanity check you can mention in an interview: the last running-total value must match SUM(amount) over the whole set.

Resetting Per Group With PARTITION BY

Real questions usually want a running total per customer or per region, not one global total. Add PARTITION BY and the accumulation restarts at the top of each partition.

The mental model: PARTITION BY splits rows into independent buckets, and the ORDER BY + frame run separately inside each bucket.

SELECT
  customer_id,
  sale_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY sale_date
  ) AS customer_running_total
FROM sales;

The Tiebreaker Trap

If two rows share the same ORDER BY value (two sales on the same date), the default RANGE frame treats them as peers and gives them the same running total, including both amounts.

If you need a strictly row-by-row increment even on ties, switch to ROWS framing and add a unique tiebreaker to the ORDER BY, such as sale_date, id. Interviewers plant duplicate dates specifically to see if you notice.

SELECT
  sale_date,
  amount,
  SUM(amount) OVER (
    ORDER BY sale_date, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

Running Total of a Count

Cumulative logic is not limited to SUM. Any aggregate works as a window function, so you can build a running count, running average, or running max.

A running count of orders is a common dashboard metric: how many orders have we taken so far as of each day?

SELECT
  order_date,
  COUNT(*) OVER (
    ORDER BY order_date
  ) AS orders_to_date
FROM orders;

The Old Way: Correlated Subquery

Interviewers sometimes ask you to solve a running total without window functions to test depth. The classic pre-window solution is a correlated subquery that re-sums every prior row.

It works but is O(n squared): for each row it rescans the table. Mention this to show you know why window functions replaced it.

SELECT
  s.sale_date,
  s.amount,
  (SELECT SUM(s2.amount)
   FROM sales s2
   WHERE s2.sale_date <= s.sale_date) AS running_total
FROM sales s
ORDER BY s.sale_date;

Filtering vs the Window Result

A frequent follow-up: "Show only days where the running total crossed 1000." You cannot put a window function in WHERE because the frame is computed after WHERE runs.

The fix is to compute the running total in a CTE or subquery, then filter the outer query. This is the same wrapping rule that applies to every window function.

WITH t AS (
  SELECT
    sale_date,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total
  FROM sales
)
SELECT *
FROM t
WHERE running_total >= 1000;

Interview Talking Points

When you deliver a running-total answer, narrate these points to score full marks:

SUM OVER (ORDER BY ...) is the cumulative form.
Adding ORDER BY creates a default frame of UNBOUNDED PRECEDING to CURRENT ROW.
Use PARTITION BY to reset per group.
Add a unique tiebreaker and ROWS framing to avoid the duplicate-value trap.
Wrap in a CTE to filter on the result.

Quick Check

Test your understanding of the default frame.

Recap: Cumulative Sums

A running total is an ordered window aggregate. SUM(amount) OVER (ORDER BY sale_date) accumulates rows from the partition start to the current row, thanks to the implicit UNBOUNDED PRECEDING-to-CURRENT ROW frame.

Reset it per group with PARTITION BY, add a tiebreaker plus ROWS framing to handle duplicate sort values, and wrap it in a CTE whenever you need to filter on the cumulative value. Next, we dissect the ROWS vs RANGE distinction that this lesson hinted at.

Frequently asked questions

Is the “Cumulative Sums With Window Frames” lesson free?

Yes — the full text of “Cumulative Sums With Window Frames” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Cumulative Sums With Window Frames”?

Building a running total using SUM OVER with an ordered frame. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Cumulative Sums With Window Frames” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

Cumulative Sums With Window Frames
ROWS vs RANGE Framing
Moving Averages Over a Sliding Window
Cumulative Distribution and Percent of Total

← Back to SQL Interview Prep