Brightlane's sales team tracks customer spending trends using a 7-order rolling average — the current order plus up to the six immediately preceding orders for that customer.
Write a query to return every order's ID, customer ID, order amount, and the average total_amount across that customer's current order plus the six immediately preceding orders chronologically.
Assumptions:
- Within each customer's orders, the rolling-7 average at each row covers that order plus the up-to-six orders with the largest
ordered_atstrictly before it. The window is restricted to that customer. - For a customer's earliest orders the window is partial: the first order's average equals that one order; the second's covers two; the count grows by one with each subsequent order until it reaches the full window of seven on the seventh order onward.
- The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, androlling_7_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 6 PRECEDING
AND CURRENT ROW
) AS rolling_7_avg
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
AVG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) produces a 7-order rolling average per customer. The frame is widened from the 3-row pattern by changing one number: 2 PRECEDING becomes 6 PRECEDING, and the trailing window now covers up to seven rows.
Clause by clause
SELECT id, customer_id, total_amount, AVG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7_avgreturns the order identifiers and the windowed average.PARTITION BY customer_idkeeps each customer's window independent;ORDER BY ordered_atsequences each customer's orders in time;ROWS BETWEEN 6 PRECEDING AND CURRENT ROWdeclares the trailing 7-row physical frame.FROM ordersreads every order.ORDER BY customer_id, ordered_atsorts the result so each customer's trend reads top to bottom.
Why ROWS and not RANGE
The prompt asks for a fixed number of orders, not a fixed number of days. ROWS counts row positions, which is what "7 orders" means. A RANGE frame with an interval would give a calendar-day window instead, which would include a varying number of orders depending on date density. The number-of-orders framing is the trigger for ROWS.
The trap
The frame grows from one row to seven as orders accumulate. The first order's average equals just that order; the seventh order's average is the first true 7-order mean. A common error is to dismiss the early-row averages as noise and try to filter them out with a WHERE clause on the window position. That filter would have to run before the window, which means the window would never see the dropped rows in the first place. The early partial-window rows are part of the answer here, not bugs to remove.
You practiced AVG OVER (... ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) — fixed-position 7-record rolling average; the window grows from 1 record up to 7 as records accumulate.
