Running Total with a Window Function
sqlSQLinterview-prepintermediate
Add up values row by row in date order using SUM() OVER with an explicit ROWS frame, which is faster and handles ties correctly.
-- Running total of order values, oldest first.
-- The explicit ROWS frame matters: the default frame (RANGE) gives tied dates the same total
-- and is slower in SQL Server. The id in ORDER BY breaks ties between orders on the same date.
SELECT id,
order_date,
total,
SUM(total) OVER (
ORDER BY order_date, id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;