Brightlane's operations team is building a rolling 3-order revenue report over the global order queue, with both the trailing sum and the count of orders contributing to that sum so the team can spot positions where the window is partial.
Write a query to return every order's ID, ordered-at timestamp, total amount, the combined total_amount across that order plus up to the two immediately preceding orders chronologically, and the count of orders included in that sum.
Assumptions:
- The rolling-3 window at each row covers that order plus the two orders with the largest
ordered_atstrictly before it across the entire stream. - For the very first order in the stream, the window holds one record, so the count is
1. For the second order, the window holds two records, so the count is2. From the third order onward, the count is3. - The final result is sorted by
ordered_atascending.
Output:
- One row per order, with columns
id,ordered_at,total_amount,rolling_3_sum, andwindow_size. 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_sum,
COUNT(*) OVER (
ORDER BY
ordered_at ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW
) AS window_size
FROM
orders
ORDER BY
ordered_atThe shape
Two window functions can share the same frame definition. SUM and COUNT(*) both use ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, which gives the trailing total alongside the number of rows that actually contributed to it. The count exposes the partial-window positions at the start of the stream as a real column instead of leaving them as silent edge cases.
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_sum, COUNT(*) OVER (ORDER BY ordered_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS window_sizereturns each order plus the two windowed values. BothOVERclauses are identical: noPARTITION BYso the window spans every order;ORDER BY ordered_atsequences the stream chronologically; the sameROWSframe applies to both aggregates.FROM ordersreads every order.ORDER BY ordered_atsorts the displayed result chronologically.
Why COUNT(*) and not COUNT(total_amount)
COUNT(*) counts rows in the frame regardless of NULLs in any column. COUNT(total_amount) would count only rows where total_amount is non-null. For a window-size diagnostic, the question is "how many rows is the sum averaging across," not "how many rows have non-null amounts." COUNT(*) answers the diagnostic question directly. If amounts could be NULL and the team needed to flag missing values, COUNT(total_amount) would be the right call instead, and the divergence between the two would itself be the signal.
The trap
The two windows look like they could collapse into one window definition reused twice, but PostgreSQL has no shared-frame syntax in this position. Each window function carries its own OVER clause and the engine optimises identical clauses behind the scenes. Trying to factor the frame out with a named WINDOW clause is possible, but the inline form is the standard idiom for two-aggregate diagnostics. The cost is duplicated text; the benefit is that the relationship between each aggregate and its frame is visible in one place.
You practiced two windowed aggregates over the same trailing frame — SUM and COUNT(*) paired against the same ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, useful for diagnosing partial-window positions at the start of a stream.
