A running total that does not lie about ties

A running total that does not lie about ties comes up often enough to be worth a note. saas-subscriptions is a convenient thing to try it on — 2,500 rows, one row per account.

select signed_up_on,
       mrr_usd,
       sum(mrr_usd) over (
         order by signed_up_on
         rows between unbounded preceding and current row
       ) as running_mrr_usd
from saas_subscriptions
order by signed_up_on;

The frame clause is not optional decoration. Leave it out and you get range between unbounded preceding and current row, which includes every peer row with the same ordering value — so on a column with duplicate timestamps the running total jumps early and no error is raised.

Run it against the real file in the SQL playground — the engine there is enough of a SQL implementation to execute this as written, and the dataset is already loaded. The pattern page has the version with the failure modes spelled out.

Window functions are the difference between SQL that describes rows and SQL that describes sequences. Almost every "we exported it to pandas to do this bit" turns out to be one of these five.