Sweaty Imposter

Running Total of Revenue

SQLWindow functionSeniorStaffUpdated
Return each day with a running total of revenue from the earliest day through that day, ordered by day.

Schema

CREATE TABLE daily_revenue (
  day     DATE PRIMARY KEY,
  amount  NUMERIC NOT NULL
);

Drill This: Your Query First

The Correct Query

Option (b)

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM daily_revenue
ORDER BY day;
A windowed SUM with ORDER BY and an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame is the canonical running total. (a) and (d) return the grand total on every row because there is no ORDER BY in the window. (c) partitions by day, so each window contains a single row and 'running_total' just equals amount.

Going Deeper

Without an explicit frame clause, ORDER BY in a window defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which collapses tied ORDER BY values into one bucket. Spelling out ROWS avoids surprises when keys repeat.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT day, amount,
       SUM(amount) OVER () AS running_total
FROM daily_revenue
ORDER BY day;

Option (c)

SELECT day, amount,
       SUM(amount) OVER (PARTITION BY day ORDER BY day) AS running_total
FROM daily_revenue
ORDER BY day;

Option (d)

SELECT day, amount,
       (SELECT SUM(amount) FROM daily_revenue) AS running_total
FROM daily_revenue
ORDER BY day;