SQL windows: running totals, prior values and ties

01 / Define what one row represents

A raw row is a transaction; an analysis row is a team’s daily aggregate. GROUP BY collapses rows, while window functions add running totals or ranks without collapsing those analysis rows. LAG over raw transactions may describe the preceding transaction rather than the preceding reporting period.

PARTITION BY separates groups, ORDER BY defines order inside a window, and ROWS defines the frame for each row. Output order still requires an outer ORDER BY; window ordering alone does not guarantee presentation order.

02 / A directly runnable exercise

Run python notebook/examples/sql_windows.py from the repository root without downloading data. A CTE first aggregates by date and team; windows then compute a running total, prior observation and daily rank. SQLite 3.25.0 or newer is required.

"""Window functions on synthetic daily aggregates, using the standard library."""
import sqlite3

QUERY = """
WITH daily AS (
  SELECT day, team, SUM(amount) AS total
  FROM sales GROUP BY day, team
)
SELECT day, team, total,
       SUM(total) OVER (
         PARTITION BY team ORDER BY day
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total,
       LAG(total) OVER (PARTITION BY team ORDER BY day) AS previous_observed,
       DENSE_RANK() OVER (PARTITION BY day ORDER BY total DESC) AS daily_rank
FROM daily ORDER BY day, team
"""

def analyze():
    with sqlite3.connect(":memory:") as db:
        db.execute("CREATE TABLE sales(day TEXT, team TEXT, amount INTEGER NOT NULL)")
        # Amounts are integer minor units. Team A has no observation on October 2.
        db.executemany("INSERT INTO sales VALUES (?, ?, ?)", [
            ("2026-10-01", "A", 10), ("2026-10-01", "A", 5),
            ("2026-10-01", "B", 15), ("2026-10-02", "B", 7),
            ("2026-10-03", "A", 20), ("2026-10-03", "B", 3),
        ])
        return db.execute(QUERY).fetchall()

if __name__ == "__main__":
    for row in analyze():
        print(row)

03 / Interpret rows, not just successful execution

A’s two October 1 transactions total 15, and its October 3 running total is 35. B’s totals are 15, 22 and 25. Both teams rank first on day one. DENSE_RANK leaves no gaps after ties; ROW_NUMBER assigns distinct numbers, so a stable tie-breaker is needed when choosing exactly one row.

A has no October 2 observation, so its October 3 LAG is October 1’s 15, not yesterday’s value. For calendar-day comparisons, join onto a date spine and decide whether missing means zero, unknown or closed; do not silently fill zeros.

04 / Window boundaries and data quality

The running total explicitly uses ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With nonunique ordering keys, a default RANGE frame may include all peers together, producing jumps. This example aggregates first so each team has one row per day.

Real analysis also needs duplicate, timezone, refund-sign and null-amount checks. Integer minor units avoid fractional representation issues here but do not define a financial policy. For large tables inspect plans and sorting costs before indexes or distributed processing; this sample says nothing about throughput.

05 / Study and written-test reflection

Start by stating the grain, then the partition and ordering, and finally check two or three counterexamples. Practice top-N per group, activity streaks and rolling seven-day metrics next. Seven preceding rows and seven calendar days are different definitions.