CTEs and windowed analysis

The idea

A common table expression names an intermediate query within one statement. It can make a multi-stage report readable by separating filtering, aggregation, and final selection. Treat it as a logical structure rather than a promise that SQLite materializes a temporary table. Inspect performance separately when needed. Clear stage names help explain why each transformation exists.

Window functions calculate across related rows while retaining individual rows in the result. PARTITION BY separates groups, and a window ORDER BY defines calculation order. This ordering does not replace the final query’s ORDER BY. For running totals, specify a ROWS frame when you mean individual ordered rows, and add a unique tie-breaker. Ranking functions and aggregate windows answer different questions, so choose them from the required output.

WITH events(id,amount) AS (VALUES(1,4),(2,3),(3,5))
SELECT id, SUM(amount) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running
FROM events ORDER BY id;

id | running
1  | 4
2  | 7
3  | 12

The explicit frame includes each row and all earlier rows ordered by id. Every original event still has its own output row.