Use LAG to calculate a change between ordered rows

By Batu · English technical notes

To use LAG to calculate a change between ordered rows in SQLite, put the business ordering inside OVER (ORDER BY ...), then subtract the lagged value. Do not rely on insertion order or an outer ORDER BY: neither defines which row LAG considers previous.

This fixture deliberately inserts the three daily totals in a non-chronological sequence. The naive query has LAG(total) OVER (); its displayed result is one possible consequence of that fixture, not an ordering guarantee. In particular, the row for 2024-01-01 receives a previous value from a later date. The corrected query orders the window by day, while its final ORDER BY day controls presentation. It returns NULL, 3, and -4, which the assertion verifies.

LAG returns NULL when the prior row in its partition does not exist. SQLite documents LAG and the distinction between a window's ordering and final result ordering in its window-functions documentation; see also the SELECT documentation. The Python side uses the standard-library sqlite3 module and an in-memory database only. No newer Python-specific API is used (Python 3.5+); the SQLite runtime must support window functions (SQLite 3.25.0+).

AI-assistance disclosure: this synthetic example was drafted with AI assistance and is intended to be run and adapted by the reader.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE daily(day TEXT PRIMARY KEY, total INTEGER)")
seed = [("2024-01-02", 13), ("2024-01-03", 9), ("2024-01-01", 10)]
con.executemany("INSERT INTO daily VALUES (?, ?)", seed)

print("schema:", con.execute(
    "SELECT sql FROM sqlite_master WHERE name = 'daily'"
).fetchone()[0])
print("seed:", seed)
naive = list(con.execute("""
    SELECT day, total, total - LAG(total) OVER () AS change
    FROM daily ORDER BY day
"""))
print("naive:", naive)
corrected = list(con.execute("""
    SELECT day, total, total - LAG(total) OVER (ORDER BY day) AS change
    FROM daily ORDER BY day
"""))
print("corrected:", corrected)

assert corrected == [
    ("2024-01-01", 10, None),
    ("2024-01-02", 13, 3),
    ("2024-01-03", 9, -4),
]
con.close()

schema: CREATE TABLE daily(day TEXT PRIMARY KEY, total INTEGER)
seed: [('2024-01-02', 13), ('2024-01-03', 9), ('2024-01-01', 10)]
naive: [('2024-01-01', 10, 1), ('2024-01-02', 13, None), ('2024-01-03', 9, -4)]
corrected: [('2024-01-01', 10, None), ('2024-01-02', 13, 3), ('2024-01-03', 9, -4)]