SQLite / Intermediate: query structure and transactional design / Lesson 11

Window functions, ranking, and frames

Calculate per-group rankings and running measures while preserving detail rows and making tie rules, frames, and final presentation explicit.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Distinguish windows from grouping.
Select one row per partition deterministically.
Choose a ROWS frame and explain neighboring values.

Keep detail rows while calculating related values

GROUP BY collapses input into group rows, while a window function calculates across related rows without inherently collapsing them. PARTITION BY defines independent groups, the window ORDER BY defines calculation sequence, and a frame can limit which rows contribute to an aggregate. The outermost ORDER BY still controls presentation. These are separate contracts even when the expressions look similar.

SQLite's baseline supports standard window syntax described in the official documentation. Window functions appear in result expressions and appropriate ordering positions, not as a shortcut around logical WHERE processing. A ranking result used for filtering normally needs another query layer. Test a partition with one row, several rows, and ties before applying the pattern to a large report.

Define the tie policy for latest-per-group selection

ROW_NUMBER gives distinct sequence positions within each partition. RANK and DENSE_RANK express different tie policies, sharing ranks rather than always selecting one arbitrary winner. A “latest order per customer” rule must state how equal timestamps are resolved. The first script orders timestamps descending and then unique identifiers descending before filtering rn=1 through a CTE.

Customer1 selects id2 from the tied date, while customer2 selects id3. The final display is ordered by customer. A unique tiebreaker is correct only if the product really wants one winner; if all equally latest records are required, use a different ranking rule and expected cardinality. Ranking is a semantic choice, not a generic duplicate-removal technique.

sql Latest row with an explicit unique tie rule

CREATE TABLE purchase(id INTEGER PRIMARY KEY,customer INTEGER,created_on TEXT,amount INTEGER);
INSERT INTO purchase VALUES(1,1,'20260101',10),(2,1,'20260101',20),(3,2,'20260102',15);
WITH ranked AS (SELECT *,ROW_NUMBER() OVER
 (PARTITION BY customer ORDER BY created_on DESC,id DESC) AS rn FROM purchase)
SELECT customer,id,amount FROM ranked WHERE rn=1 ORDER BY customer;

Expected output

customer|id|amount
1|2|20
2|3|15

Id2 wins the equal-date customer1 tie; id3 is the only row for customer2. The outer query controls display order.

Make accumulation boundaries visible

SUM(amount) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) accumulates from the partition start through the current row under that row ordering. The second script produces10,30,35 and uses LAG to expose the prior row's amount. The first prior value is NULL because no previous row exists; choosing a display fallback is a separate policy.

Default frames can include peer groups under RANGE semantics when ordering values tie. A running total can therefore jump for all equal-order peers instead of advancing one row at a time. Choose an explicit ROWS frame and a deterministic sequence when individual progression is intended. Do not assume the same frame is meaningful for every built-in ranking or offset function; read the documented function behavior.

sql An explicit running frame and previous value

CREATE TABLE movement(id INTEGER PRIMARY KEY,amount INTEGER);
INSERT INTO movement VALUES(1,10),(2,20),(3,5);
SELECT id,amount,
 SUM(amount) OVER(ORDER BY id ROWS UNBOUNDED PRECEDING) AS running,
 LAG(amount) OVER(ORDER BY id) AS previous
FROM movement ORDER BY id;

Expected output

id|amount|running|previous
1|10|10|NULL
2|20|30|10
3|5|35|20

Each current amount is added to earlier rows. LAG reads the prior row, which does not exist for id1.

Distinguish last-in-frame from last-in-partition

Functions such as first_value and last_value depend on the frame, so “last value” may mean the last row in the current frame rather than the final row in the entire partition. State whether future rows should contribute. A full-partition last value requires an appropriate frame ending at UNBOUNDED FOLLOWING, along with a defined ordering and tie policy.

An offset such as LAG describes a row position, not necessarily a calendar interval. If dates are missing, the prior row is not automatically yesterday. Build a calendar or explicitly defined interval model when the question concerns time gaps. Missing values inside an existing row are also different from a missing row, and a default argument can conceal that distinction if used carelessly.

Verify detail identity and sorting cost

Compare window output to hand-worked partitions containing ties, a missing value, and uneven group sizes. Preserve stable identifiers so a computed rank is not confused with permanent row identity. A result calculated on a filtered subset answers a different question from ranking all rows and filtering afterward; choose the stage intentionally.

Window ordering can require sorting or useful composite indexes, but a shorter expression does not guarantee less work. Inspect a representative plan and compare result correctness first. Named windows can clarify repeated definitions without making performance promises. The final report should state partition, ordering, frame, missingness, and whether the calculation preserves or deliberately changes the detail population.

Common errors and fixes

A latest-row winner changes on tied timestamps.

Why it happens: The ranking order is incomplete.
How to fix it: Add the intended unique tie rule or retain ties with a suitable rank.
Prevent it next time: Test equal-order values.

A running total jumps across equal values.

Why it happens: The selected/default frame includes peers.
How to fix it: Use the intended ROWS frame and deterministic order.
Prevent it next time: Review frame boundaries explicitly.

The previous row was called yesterday.

Why it happens: Row offset was mistaken for a calendar interval.
How to fix it: Define missing-date handling and calendar structure.
Prevent it next time: Test gaps in dates.

Check your understanding

1. For descending scores90,90,80, what do RANK and DENSE_RANK produce?

Show worked solution

RANK produces1,1,3 and DENSE_RANK1,1,2. ROW_NUMBER assigns distinct positions and needs a tie rule to choose between the two90 rows. Select the policy from whether the business retains ties or chooses one row.

2. Why can LAST_VALUE with a default ordered frame fail to mean the final partition value?