ROWS, RANGE and peer-aware running totals

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

ROWS, RANGE and peer-aware running totals

Concept and contract

Make the intended frame explicit when ordered aggregates encounter equal sort values.

A window frame chooses which rows contribute to an aggregate for the current row. Company A has two observations on the same first day, then one on a later day. This fixture exposes the difference between physical ordered row progression and peer-aware date progression.

The worked query shows both totals. The ROWS window orders by day and unique id, producing 2, 5 and 6. The RANGE window orders only by day, so both first-day peers see the complete same-day total five before the final total six.

Mechanism and worked example

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes the sequence through the current ordered row.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW includes the sequence through the current ordered row. Its unique ordering key makes the row progression deterministic. RANGE with a date-only ordering groups equal ordering values as peers for the unbounded-to-current frame used here.

The windows intentionally use different peer definitions. If id were added to the RANGE ordering too, those first-day rows would cease to be peers. Frame interpretation depends on the complete window ordering, not just on the keyword ROWS or RANGE in isolation.

Complete worked example

sql

SELECT id, SUM(added) OVER (ORDER BY day,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_total,
 SUM(added) OVER (ORDER BY day RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS day_total
FROM observations WHERE company_id='A' ORDER BY id;

Reasoning: The first same-day peer sees ROWS total two but RANGE peer total five.

Counterexamples and common mistakes

The counterexample uses the row-by-row frame for both output measures.

The counterexample uses the row-by-row frame for both output measures. It labels the first observation's value two as if it were the completed same-day peer total five. The mistake is a wrong measure definition, not a random engine difference.

Do not rely on a default frame when the report's meaning depends on peer groups. Also avoid extending this example to offset RANGE intervals without checking dialect-specific type and ordering restrictions. The shared lab uses only the widely supported unbounded-to-current form.

Counterexample: a plausible but wrong query

sql

SELECT id, SUM(added) OVER (ORDER BY day,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS row_total,
 SUM(added) OVER (ORDER BY day,id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS day_total
FROM observations WHERE company_id='A' ORDER BY id;

Reasoning: Repeating the ROWS calculation does not compute the requested peer total.

Independent practice: small query

Attach the complete company A observation total to each of its three detail rows.

Attach the complete company A observation total to each of its three detail rows. Predict six on every row. This independent task removes ordering from the aggregate window so the complete selected partition, rather than a running prefix, contributes each time.

Keep the company A filter and source identities. Do not replace the window with a plain SUM aggregate, which would collapse the detail into one row. The task is deliberately smaller than writing another frame with additional offset rules.

Step 1: Keep detail identities and the company filter. Step 2: Compute a full-population window total without ordering.

Practice starter: solve before reading the solution

sql

SELECT id, SUM(added) OVER (/* whole selected partition */) AS total
FROM observations WHERE company_id='A' ORDER BY id;

Reasoning: Remove window ordering to measure the complete selected population.

Hint 1: A plain SUM without OVER would collapse the details.
Hint 2: Use OVER () for this already-filtered single-company input.

Solution, tests and trade-offs

The solution uses SUM(added) OVER () after filtering to company A.

The solution uses SUM(added) OVER () after filtering to company A. It returns identities 1, 2 and 3, each with six. The empty window specification covers the complete selected population in this example, and the final ORDER BY id controls display.

Compare those repeated totals with both worked running measures. All three end at six, so checking only the last row would miss the incorrect intermediate semantics. The first peer row is the critical acceptance case for this fixture.

Practice solution and acceptance checks

sql

SELECT id, SUM(added) OVER () AS total
FROM observations WHERE company_id='A' ORDER BY id;

Reasoning: Each retained A observation carries the same whole-population total six.

Interview reasoning and next step

In an interview, draw the ordered rows and circle the frame contributing to one result.

In an interview, draw the ordered rows and circle the frame contributing to one result. Include a tie so a default or incomplete explanation cannot pass accidentally. State whether the requested report is running by record, cumulative by value peers or full-partition annotation.

The next lesson compares adjacent observations with LAG. A predecessor in the observed sequence is not automatically the previous calendar day, so temporal interpretation still requires an explicit population and interval policy.

Optional stretch — after the core task works: Add a unique ordering key to RANGE and explain how it changes peer equality.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Make the intended frame explicit when ordered aggregates encounter equal sort values.