lesson 37 Window functions · advanced

Running totals

Add values progressively in a defined order.

the idea

SUM(amount) OVER (ORDER BY … ROWS UNBOUNDED PRECEDING) accumulates a running total row by row. PARTITION BY restarts it for each account or period. Adding the opening balance turns cumulative movements into a running balance.

Dataset: Finance / Payments

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   See account 3’s transactions. Run this. Deposits show as positive cents and spending as negative, in the order they posted.

   step-1.sql Run

   sql›

   SELECT transaction_id, transaction_type, status, amount_cents, posted_at
   FROM transactions
   WHERE account_id = 3
   ORDER BY posted_at, transaction_id
   LIMIT 15;

2. 2

   Build a running total. Run this. SUM() OVER adds each posted amount to everything before it on this account, row by row—that is a running balance of movements only.the pattern

   example.sql Run

   sql›

   SELECT
     transaction_id,
     posted_at,
     amount_cents,
     SUM(amount_cents) OVER (ORDER BY posted_at, transaction_id ROWS UNBOUNDED PRECEDING) AS cumulative_cents
   FROM transactions
   WHERE account_id = 3 AND status = 'posted';

common mistake

Without a unique tie-breaker, entries posted at the same instant can be accumulated in any order; with the default RANGE frame, tied rows also share one total.