Running total in SQL: one window, no self-join

"Revenue to date" is one of the most common SQL interview questions. It sounds like it needs a self-join, and it does not: one window function does the job in one pass. This page covers the window, the PARTITION BY for one total per customer, and the frame clause that decides what happens on a tie. The print-ready A4 PDF is at the bottom.

The question

• Revenue to date: one row per day, with the total so far.
• The old way is a self-join: each row joined to all earlier rows.
• That grows as N squared, so it gets slow fast.

The answer

• SUM(x) OVER (ORDER BY day) gives the total so far, in one pass.
• PARTITION BY customer gives one running total per customer.
• ROWS UNBOUNDED PRECEDING makes the frame explicit. Say it, or ties share a total.

The running total, per customer

SELECT customer_id, day, amount,
  SUM(amount) OVER (
    PARTITION BY customer_id
    ORDER BY day
    ROWS UNBOUNDED PRECEDING
  ) AS revenue_to_date
FROM orders;

The window is ordered by day and restarted for each customer. The frame clause tells the database to add row by row.

Without ROWS UNBOUNDED PRECEDING, the frame is RANGE. Two orders on the same day are then peers, and both show the total that includes both. This is usually not what the question meant.

Remember

• RANGE is the default frame: rows with the same value are peers.
• ROWS goes row by row, which is what you meant.

The trap: two orders, same day

Same query, two frames, two answers on the tie.

With RANGE, the first Oct 2 order already shows 50, because its peer on the same day is counted too. With ROWS, the total grows by one order at a time: 10, then 30, then 50.

If you want a total per day and not per order, aggregate to one row per day first, then apply the window. Then there are no ties left to worry about.

The quiz

Same day, same total. Which one?

A) SUM(amount) OVER (
     ORDER BY day)

B) SUM(amount) OVER (
     ORDER BY day
     ROWS UNBOUNDED PRECEDING)

A. The default frame is RANGE: two rows on the same day are peers, so both get the total that includes both of them. B moves row by row, so each row adds exactly itself.

Interview phrasing worth memorizing: I order by day and use ROWS UNBOUNDED PRECEDING so each row adds exactly itself, ties included.