Brightlane's fulfillment team analyzes delivered order spending patterns only — pending and cancelled orders are excluded.
Write a query to return every delivered order's ID, customer ID, amount, and the average total_amount across that customer's current delivered order plus the two immediately preceding delivered orders chronologically.
Assumptions:
- A delivered order has
status = 'delivered'. Only delivered orders should appear in the result, and only delivered orders should contribute to the rolling-3 average. - Within each customer's delivered orders, the rolling-3 average at each row covers that delivered order plus the two delivered orders with the largest
ordered_atstrictly before it. The window is restricted to that customer's delivered subset. - For a customer's first delivered order, the average equals that one order's amount. The second covers two; from the third onward the window covers three.
- The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per delivered order, with columns
id,customer_id,total_amount, androlling_3_avg. Sorted bycustomer_id, thenordered_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,
customer_id,
total_amount,
AVG(total_amount) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW
) AS rolling_3_avg
FROM
orders
WHERE
status = 'delivered'
ORDER BY
customer_id,
ordered_atThe shape
WHERE status = 'delivered' runs before the window, so the window only sees delivered orders. The rolling-3 average then operates over that already-filtered subset per customer, treating non-delivered orders as if they never existed.
Clause by clause
SELECT id, customer_id, total_amount, AVG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_3_avgreturns the order identifiers and the rolling average.PARTITION BY customer_idisolates each customer's window;ORDER BY ordered_atsequences the surviving rows chronologically;ROWS BETWEEN 2 PRECEDING AND CURRENT ROWis the trailing 3-row frame.FROM ordersreads every order in the table.WHERE status = 'delivered'keeps only delivered rows. This runs before the window function evaluates, so the window's "preceding two rows" come from the delivered subset, not from the raworderstable.ORDER BY customer_id, ordered_atsorts the displayed result so each customer's delivered history reads top to bottom.
The trap
The execution order is what makes this work cleanly. WHERE runs before OVER, so non-delivered orders are gone by the time the window starts counting "two rows back." If the requirement were the opposite — include every order in the window but flag the delivered ones — the filter would have to live inside the aggregate using CASE WHEN, not in WHERE. Putting the filter in the wrong place silently changes what "two preceding orders" means.
You practiced a rolling-record frame applied after a WHERE restriction — the WHERE runs first, so the window operates over only the surviving records.
