SQLite Basics: Rank Rows and Calculate Running Totals

A window function calculates across related rows while keeping each detail row in the result. Use one when a report needs both the individual event and context such as its position, cumulative amount, or previous value.

The OVER clause defines which rows belong together and how they are ordered for the calculation. That calculation order is separate from the final query’s display order, so these examples specify both.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. No packages are needed. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this small runner as run-sql.py. It executes SQL against a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. Put each statement’s ending semicolon at the end of its own line, as below; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Number rows within each team

Save as team-ranking.sql:

WITH scores(id, team, name, points) AS (
  VALUES (1, 'Blue', 'Mina', 90), (2, 'Blue', 'Alex', 90),
         (3, 'Blue', 'Jo', 75), (4, 'Red', 'Sam', 80)
)
SELECT team, name, points,
       ROW_NUMBER() OVER (
         PARTITION BY team ORDER BY points DESC, id
       ) AS position
FROM scores
ORDER BY team, position;

Run:

python3 run-sql.py team-ranking.sql

Output:

Blue|Mina|90|1
Blue|Alex|90|2
Blue|Jo|75|3
Red|Sam|80|1

PARTITION BY team restarts numbering for each team. The unique ID settles the tie between Mina and Alex, so their row numbers are predictable. Each input row remains visible.

Use ROW_NUMBER for a distinct sequence. If equally scoring people should share a rank, investigate RANK or DENSE_RANK with ordering based on points alone. Including a unique ID in that ranking order would remove the tie you intended to recognize.

2. Add a running total

Save as running-total.sql:

WITH sales(id, day, cents) AS (
  VALUES (1, '2026-10-01', 1000),
         (2, '2026-10-01', 500),
         (3, '2026-10-02', 700)
)
SELECT id, day, cents,
       SUM(cents) OVER (
         ORDER BY day, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_cents
FROM sales
ORDER BY day, id;

Run:

python3 run-sql.py running-total.sql

Output:

1|2026-10-01|1000|1000
2|2026-10-01|500|1500
3|2026-10-02|700|2200

The frame includes every earlier row plus the current row. Two sales share a date, so the ID gives them an unambiguous sequence. Writing the ROWS frame explicitly makes the per-event intention visible.

A default frame can include peers with equal ordering values, which may make totals jump together rather than advance one event at a time. For separate customer balances, add a customer partition as well as the ordering and frame.

3. Compare each reading with the previous one

Save as previous-reading.sql:

WITH readings(day, value) AS (
  VALUES ('2026-10-01', 20), ('2026-10-02', 23),
         ('2026-10-04', 21)
)
SELECT day, value,
       LAG(value) OVER (ORDER BY day) AS previous_value,
       value - LAG(value) OVER (ORDER BY day) AS change
FROM readings
ORDER BY day;

Run:

python3 run-sql.py previous-reading.sql

Output:

2026-10-01|20|NULL|NULL
2026-10-02|23|20|3
2026-10-04|21|23|-2

LAG accesses the preceding row in the window order. The first row has no predecessor, so both the previous value and the difference are missing. Leaving that missing value visible avoids pretending that the first reading followed a zero.

Notice the gap between October 2 and October 4. This is a change from the previous recorded reading, not necessarily the previous calendar day. A date-aware daily comparison needs a calendar or an explicit date-based join.

See the official window function reference. To filter by a calculated window value, put the window query in a subquery or CTE and filter its result in an outer query.

Clean up

In the same terminal, remove the example files and the empty scratch directory:

rm -- run-sql.py team-ranking.sql running-total.sql previous-reading.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.