Brightlane's finance team monitors revenue velocity using a 30-day rolling total — every order paired with the combined revenue of every other order placed in the trailing 30-calendar-day window.
Write a query to return every order's ID, ordered-at timestamp, total amount, and the combined total_amount across every order whose ordered_at falls within the 30 calendar days ending on and including that order's ordered_at.
Assumptions:
- The rolling 30-day window covers every order whose
ordered_atis within29calendar days before the current row'sordered_at, plus the current order itself. - The window is calendar-based rather than position-based: gaps in the date sequence reduce the order count in the window; clusters of orders on the same day all contribute together.
- The final result is sorted by
ordered_atascending.
Output:
- One row per order, with columns
id,ordered_at,total_amount, androlling_30d_sum. 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 RANGE BETWEEN INTERVAL '29 days' PRECEDING
AND CURRENT ROW
) AS rolling_30d_sum
FROM
orders
ORDER BY
ordered_atThe shape
RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW makes the frame value-based instead of position-based. The window covers every order whose ordered_at falls inside the 30 calendar days ending on the current row's date, regardless of how many orders that range contains.
Clause by clause
SELECT id, ordered_at, total_amount, SUM(total_amount) OVER (ORDER BY ordered_at RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW) AS rolling_30d_sumreturns each order with the calendar-window sum attached. The window has noPARTITION BY, so it spans every order;ORDER BY ordered_atis the value the frame measures distance against; theRANGEframe says "every row whose ordering value is within 29 days before mine, plus mine."FROM ordersreads every order.ORDER BY ordered_atsorts the result chronologically for display.
Why RANGE and not ROWS
ROWS BETWEEN 29 PRECEDING AND CURRENT ROW would sum the current order plus the 29 physically prior orders, regardless of their dates. If orders cluster on certain days and gaps appear on others, those 30 rows could span 60 days or 10 days depending on the data. The finance team wants a 30-calendar-day metric, so the frame has to be measured in days, not in row positions.
The trap
RANGE requires that the ORDER BY expression and the frame offset be type-compatible. ordered_at is a timestamp, and the offset is an INTERVAL, which matches. Try the same frame with an integer offset against a timestamp ordering and PostgreSQL raises an error. The interval-against-timestamp pairing is what makes calendar-window frames work; the pairing is load-bearing.
You practiced RANGE BETWEEN INTERVAL '29 days' PRECEDING AND CURRENT ROW — a value-based frame measured in calendar days; the window's record count varies with how many records fall into the calendar range.
