Brightlane's finance team wants to compare two rolling revenue metrics side by side — one based on a fixed number of orders (the current order plus the two immediately preceding orders by position) and one based on a 30-day calendar window (every order whose date falls in the trailing 30-calendar-day range).
Write a query to return every order's ID, ordered-at timestamp, total amount, the position-based rolling-3 sum, and the 30-day calendar rolling sum.
Assumptions:
- The position-based rolling-3 sum at each row covers that order plus the two orders with the largest
ordered_atstrictly before it. Three records always, except for the first two rows in the stream where the window is partial. - The 30-day calendar rolling sum at each row covers every order whose
ordered_atis within29calendar days before the current row'sordered_at, plus the current order. The number of records varies with how many fall into the calendar range — gaps in the date sequence reduce the count; clustered orders increase it. - On rows where the position-based and calendar-based windows happen to contain different sets of orders, the two sums will differ — that divergence is the point of the side-by-side comparison.
- The final result is sorted by
ordered_atascending.
Output:
- One row per order, with columns
id,ordered_at,total_amount,rolling_3_orders, androlling_30d. Sorted byordered_at.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
ordered_at,
total_amount,
SUM(total_amount) OVER (
ORDER BY
ordered_at ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW
) AS rolling_3_orders,
SUM(total_amount) OVER (
ORDER BY
ordered_at RANGE BETWEEN INTERVAL '29 days' PRECEDING
AND CURRENT ROW
) AS rolling_30d
FROM
orders
ORDER BY
ordered_atThe shape
Two windowed sums over the same ORDER BY produce a side-by-side comparison of position-based and calendar-based rolling totals. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW counts three physical rows. RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW counts every row inside a 30-day calendar span. Both run in the same query; the divergence between them is the analytical point.
Clause by clause
SELECT id, ordered_at, total_amount, SUM(total_amount) OVER (ORDER BY ordered_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3_orders, SUM(total_amount) OVER (ORDER BY ordered_at RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW) AS rolling_30dreturns the order identifiers and the two windowed sums. The firstOVERuses aROWSframe: exactly three row positions. The second uses aRANGEframe: every row whoseordered_atis within a 29-day interval of the current row. Both share the global ordering onordered_at.FROM ordersreads every order.ORDER BY ordered_atsorts the result chronologically so the two trends read in parallel.
Why both frames and not one
Each mode answers a different question. ROWS answers "what were the last three orders' worth of revenue?" RANGE answers "what was revenue inside the last 30 calendar days?" On a steady one-order-per-day stream they would agree. The moment gaps or clusters appear, they diverge: a slow week shrinks the calendar window's row count while the position window keeps grabbing three rows from further back; a busy day swells the calendar window past three rows while the position window stays at three. The point of running both is to make that divergence quantitative.
The trap
Both queries succeed silently with whatever data exists. Neither raises an error when gaps appear. The 30-day calendar sum on a quiet stretch can drop to a single row's value, and a reader skimming the column would not see anything wrong until they noticed the rolling-3 column reporting a much larger number for the same row. The bug class is "the query runs, the numbers look plausible, the meaning is different from what the reader assumed." The fix is to read the frame clause, not the column name. rolling_30d and rolling_3_orders mean what their frames say they mean, not what an analyst's intuition reaches for first.
You practiced ROWS vs RANGE over the same ORDER BY — ROWS counts records (always exactly N when the window is full); RANGE counts a value range (record count varies with how data clusters).
