How do you compute a running total, and what is a window frame?
Use sum(x) OVER (ORDER BY ...). The frame decides which rows around the current one are included; with ORDER BY the default is from the start up to the current row (including peers with equal values), which gives a running total.
Explain it simply
A passbook shows a running balance: after each transaction, the total so far. A window frame is the slice of rows an aggregate looks at for each row. For a running total the slice is "everything from the first row to me".
You can also define a moving window, such as "this row and the 2 before it" for a 3-day moving average.
Example
SELECT day, amount,
sum(amount) OVER (ORDER BY day) AS running_total,
avg(amount) OVER (
ORDER BY day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM payments;
-- day | amount | running_total | moving_avg_3
-- 1 | 100 | 100 | 100.0
-- 2 | 200 | 300 | 150.0
-- 3 | 300 | 600 | 200.0
-- 4 | 400 | 1000 | 300.0A subtle point: the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which treats rows with equal ORDER BY values as peers, so tied dates share the same running total. Writing ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW makes each row distinct.
What to say in the interview
"A running total is sum() OVER (ORDER BY ...). The frame sets which rows are aggregated. The default RANGE frame groups tied values together, so I specify ROWS explicitly when I need strict row-by-row behaviour."
Common mistakes
- Missing that ties in the ORDER BY column produce equal running totals under the default frame.