Calculating Running Totals and Cumulative Sums in SQL

-- Example for daily sales data: date, amount
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date) AS running_total,
    AVG(amount) OVER (ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg_3_days
FROM
    daily_sales
ORDER BY
    sale_date;

-- Explanation of the OVER clause:
-- ORDER BY sale_date: Defines the order within the window.
-- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: Defines a "sliding window" for the moving average,
-- including the current row and the 2 rows before it.
-- If no frame is specified for SUM, the default is RANGE UNBOUNDED PRECEDING AND CURRENT ROW,
-- which means it sums all values from the start of the partition up to the current row.

How it works: This snippet demonstrates the calculation of running totals and cumulative sums using SQL window functions, a highly useful technique for trend analysis and financial reporting. The `SUM(amount) OVER (ORDER BY sale_date)` syntax creates a cumulative sum, adding each `amount` to the sum of all preceding `amounts` based on the `sale_date` order. Additionally, it shows how to calculate a "moving average" over a defined window (e.g., the last 3 days) using `ROWS BETWEEN ...`. Window functions operate on a set of table rows related to the current row without grouping them into a single output row, allowing for complex analytical queries that are difficult or impossible with standard aggregate functions.