Chapter 07 · SQLite Expressions, Functions, CTEs, Window Functions, and Dialect Features
Window Functions and Analytic Queries in SQLite
Build row-preserving analytic queries with ranking, offsets, frames, named windows, moving calculations, and explicit frame semantics that avoid common SQLite surprises.
Learning outcomes
A GROUP BY aggregate usually reduces many input rows into fewer result rows. A window function instead computes across a related set of rows while keeping each detail row visible. This makes ranking, running totals, moving metrics, and previous-row comparisons natural.
Explain OVER, PARTITION BY, ORDER BY, and frame specifications.
Use row_number(), rank(), and dense_rank() and explain ties.
Use lag()/lead() for previous/next-row comparisons.
Use aggregate windows for running totals and moving windows.
Avoid default-frame surprises with last_value() and peer rows.
Reuse named windows and understand where FILTER is allowed.
Aggregation collapses; windows preserve
DROP TABLE IF EXISTS analytic_reading;CREATE TABLE analytic_reading( reading_id INTEGER PRIMARY KEY, device_code TEXT NOT NULL, observed_at TEXT NOT NULL, value REAL NOT NULL);INSERT INTO analytic_reading(device_code,observed_at,value) VALUES('PUMP-007','2026-08-12T01:00:00Z',10.0),('PUMP-007','2026-08-12T02:00:00Z',12.0),('PUMP-007','2026-08-12T03:00:00Z',12.0),('PUMP-007','2026-08-12T04:00:00Z',15.0),('FAN-014','2026-08-12T01:30:00Z',5.0),('FAN-014','2026-08-12T02:30:00Z',7.0),('FAN-014','2026-08-12T03:30:00Z',6.0);-- Collapses to one row per device:SELECT device_code, avg(value) FROM analytic_reading GROUP BY device_code;-- Preserves every reading:SELECT reading_id,device_code,observed_at,value, avg(value) OVER (PARTITION BY device_code) AS device_avgFROM analytic_readingORDER BY device_code, observed_at;The presence of OVER makes the aggregate operate as a window function. Every reading remains visible alongside its device-level average.
PARTITION BY chooses groups; ORDER BY chooses sequence
A window partition is the set of rows considered together. The window ORDER BY establishes analytical sequence inside each partition; it does not replace the query's final ORDER BY for presentation.
SELECT device_code, observed_at, value, row_number() OVER ( PARTITION BY device_code ORDER BY observed_at ) AS seqFROM analytic_readingORDER BY device_code, observed_at;row_number() starts again at 1 for each device because the partition changes.
row_number, rank, and dense_rank handle ties differently
SELECT device_code, observed_at, value, row_number() OVER (PARTITION BY device_code ORDER BY value DESC) AS row_no, rank() OVER (PARTITION BY device_code ORDER BY value DESC) AS rnk, dense_rank() OVER (PARTITION BY device_code ORDER BY value DESC) AS dense_rnkFROM analytic_readingWHERE device_code='PUMP-007'ORDER BY value DESC, observed_at;For PUMP-007, the two values of 12 are peers. row_number() still gives them distinct sequence numbers; rank() gives them the same rank and leaves a gap afterward; dense_rank() gives the same rank but no gap. Add deterministic tie-break columns when business output requires a stable total ordering.
lag and lead compare neighboring rows without a self-join
SELECT device_code, observed_at, value, lag(value) OVER w AS previous_value, lead(value) OVER w AS next_value, value - lag(value) OVER w AS delta_from_previousFROM analytic_readingWINDOW w AS (PARTITION BY device_code ORDER BY observed_at)ORDER BY device_code, observed_at;The first row in each partition has no previous row, so lag() returns NULL by default. lag() and lead() use the window ordering to locate neighboring rows and ignore the frame specification.
The default frame is a source of real surprises
When a window has ORDER BY and you omit an explicit frame, SQLite's default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE includes peers with the same ORDER BY values. For a row-by-row running total, state ROWS explicitly.
DROP TABLE IF EXISTS sales_frame;CREATE TABLE sales_frame(id INTEGER PRIMARY KEY, minute INTEGER, amount INTEGER);INSERT INTO sales_frame(minute,amount) VALUES (1,10),(2,20),(2,5),(3,7);SELECT id,minute,amount, sum(amount) OVER (ORDER BY minute) AS default_range_total, sum(amount) OVER ( ORDER BY minute, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS explicit_row_totalFROM sales_frameORDER BY minute,id;At minute 2, the default RANGE frame can include both peer rows together, so the first minute-2 row can already show the total including the second minute-2 row. The explicit ROWS frame with a deterministic minute,id ordering advances one row at a time.
last_value() means last row in the frame, not automatically last in the partition
first_value(), last_value(), and nth_value() are among the built-in window functions that actually honor frame boundaries. With the default frame ending at the current row, last_value() often returns the current row's value—not the final value in the partition.
SELECT device_code, observed_at, value, last_value(value) OVER ( PARTITION BY device_code ORDER BY observed_at ) AS default_last, last_value(value) OVER ( PARTITION BY device_code ORDER BY observed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS partition_last, first_value(value) OVER ( PARTITION BY device_code ORDER BY observed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS partition_first, nth_value(value,2) OVER ( PARTITION BY device_code ORDER BY observed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS second_valueFROM analytic_readingORDER BY device_code, observed_at;If you mean “final reading for this device,” write a frame that reaches UNBOUNDED FOLLOWING. Naming the frame is part of the business meaning.
Running totals and moving windows
SELECT device_code, observed_at, value, sum(value) OVER ( PARTITION BY device_code ORDER BY observed_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_sum, round(avg(value) OVER ( PARTITION BY device_code ORDER BY observed_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ),2) AS moving_avg_3FROM analytic_readingORDER BY device_code, observed_at;The moving frame is based on rows, not clock duration. “Last three readings” and “last three hours” are different requirements. For irregular sampling, a row-count window may not represent a time window.
Named windows reduce repetition
A WINDOW clause can name a shared partition/order definition and derive more specific windows from it.
SELECT device_code, observed_at, value, row_number() OVER base AS seq, lag(value) OVER base AS prev, sum(value) OVER running AS running_sumFROM analytic_readingWINDOW base AS (PARTITION BY device_code ORDER BY observed_at), running AS (base ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)ORDER BY device_code, observed_at;Named windows improve consistency when several analytic expressions must use exactly the same partition/order definition.
FILTER works for aggregate windows, not built-in ranking/value functions
SELECT device_code, observed_at, value, sum(value) FILTER (WHERE value >= 10) OVER (PARTITION BY device_code) AS high_value_sumFROM analytic_readingORDER BY device_code, observed_at;SQLite permits FILTER on aggregate window functions. It is a syntax error to attach FILTER directly to built-in window functions such as row_number(), rank(), lag(), or last_value(). Window functions also cannot use DISTINCT.
Analytic lab: rank, delta, moving average, and endpoint
SELECT device_code, observed_at, value, dense_rank() OVER ( PARTITION BY device_code ORDER BY value DESC ) AS value_rank, value - lag(value) OVER ( PARTITION BY device_code ORDER BY observed_at ) AS delta, round(avg(value) OVER ( PARTITION BY device_code ORDER BY observed_at ROWS BETWEEN 1 PRECEDING AND CURRENT ROW ),2) AS avg_last_2, last_value(value) OVER ( PARTITION BY device_code ORDER BY observed_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS final_readingFROM analytic_readingORDER BY device_code, observed_at;Verify one partition manually before trusting the whole report. For PUMP-007, readings are 10, 12, 12, 15; the first delta is NULL, then 2, 0, 3; the two-row moving averages are 10, 11, 12, and 13.5; the final-reading value is 15 on every row in that partition.
Window checkpoint
Identify the window semantics.
- Why does a window aggregate preserve detail rows?
- What is the default frame when ORDER BY is present?
- Why can last_value() look like the current value?
- Do lag() and lead() depend on the frame?
- Can FILTER be attached to row_number()?
Review the answers
OVER changes the aggregate into a window calculation while each input row remains in the result. The default is RANGE UNBOUNDED PRECEDING through CURRENT ROW. last_value() sees only the current frame unless you extend it to UNBOUNDED FOLLOWING. lag()/lead() use ordering and ignore the frame. FILTER is not allowed on built-in window functions such as row_number(); it is allowed on aggregate windows.
Production judgment and bridge to Chapter 8
Analytic SQL can replace many fragile application loops, but its cost depends on partition size, ordering, indexes, and required sorts. Do not infer performance from elegance alone. Chapter 10 will use EXPLAIN QUERY PLAN to inspect execution strategy. Before that, Chapter 8 turns to transactions, where the expressions and write patterns from Chapters 5–7 must behave atomically under failures and concurrency.