Snippet

Calculating Running Totals with Window Functions

Window functions perform calculations across a set of table rows related to the current row without collapsing the result set into a single row. The OVER clause combined with PARTITION BY and ORDER BY creates an aggregated running total per account over time.

snippet.sql

sql

SELECT
    transaction_id,
    account_id,
    amount,
    SUM(amount) OVER (
        PARTITION BY account_id
        ORDER BY transaction_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance
FROM transactions;

Breakdown

SUM(amount) OVER (

Initiates a windowed summation over a specified frame of rows.

PARTITION BY account_id

Resets the calculation boundary for each unique account identifier.

ORDER BY transaction_date

Sorts records chronologically to calculate the balance sequentially.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Defines the sliding frame from the first partition row up to the current record.

Previous snippet Next snippet

More Intermediate SQL exercises →

Calculating Running Totals with Window Functions

Related