1 · Reporting patterns and edge cases · 25 MIN

Compare each event with its predecessor

Compare each event with its predecessor

LAG reads a value from an earlier row in the same ordered partition without collapsing rows. Partitioning by category keeps one category from borrowing another’s history. ORDER BY id makes the predecessor deterministic. Arithmetic involving a NULL predecessor or current amount remains NULL; that is an unknown difference, not automatically zero. The first event has no predecessor and should be handled deliberately by the presentation layer.

Define the output row grain and ordering before choosing SQL operators.

Read the example

SELECT id, category, amount, amount - LAG(amount) OVER (PARTITION BY category ORDER BY id) FROM events ORDER BY id;

[[1,"food",5,null],[2,"travel",3,null],[3,"food",7,2],[4,"travel",null,null],[5,"food",5,-2]]

Your challenge

Return id, category, amount and amount minus the previous amount within that category ordered by id. Keep NULL differences as NULL.

Solution cost: Aggregation scans input; window ordering can add O(n log n) sorting. Inspect the database plan. time · Grouping or window buffers depend on the plan and partition size. space

Common trap

Omitting a tie-breaker from window ordering can make predecessor selection ambiguous.

Further reading: SQLite window functions