Brightlane's revenue analytics team flags orders that deviate from a customer's recent spending pattern — a positive number means the order exceeded the customer's recent rolling average; a negative number means it fell below.
Write a query to return every order's ID, customer ID, order amount, and the difference between that order's total_amount and the average across that customer's current order plus the two immediately preceding orders chronologically.
Assumptions:
- Within each customer's orders, the rolling-3 average at each row covers that order plus the two orders with the largest
ordered_atstrictly before it. The window is restricted to that customer. - The diff-from-rolling-avg at each row is the current
total_amountminus that rolling-3 average. - For a customer's first order, the diff equals
0because the average is just that one order. For the second order, the average covers two orders and the diff reflects deviation from that two-order average. - The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, anddiff_from_rolling_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,
total_amount - AVG(total_amount) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW
) AS diff_from_rolling_avg
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
Subtracting a windowed aggregate from a column value is a single-pass deviation calculation. total_amount - AVG(total_amount) OVER (...) returns the gap between this order and the customer's recent rolling average without any join or subquery.
Clause by clause
SELECT id, customer_id, total_amount, total_amount - AVG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS diff_from_rolling_avgreturns the order identifiers and the deviation. The window function computes the rolling-3 average per row, and the surrounding subtraction expression runs that same row'stotal_amountagainst it. The whole expression evaluates per row, producing one deviation value each.FROM ordersreads every order.ORDER BY customer_id, ordered_atsorts the result for display.
Why a windowed aggregate and not GROUP BY
A GROUP BY customer_id would collapse each customer to one row and lose the per-order detail the analytics team needs. The window function keeps every row intact while still computing a customer-scoped aggregate next to each one. That row-preserving behaviour is the entire reason OVER exists.
The trap
On a customer's first order, the rolling-3 average is just that one order's amount, so the deviation is exactly zero. That's not a missing value or a calculation error. It's the only number the window can produce when the frame holds a single row. Treating those zeros as outliers and dropping them would erase the start of every customer's history. The deviation is meaningful from the second order onward, when the average actually has another data point to compare against.
You practiced inline arithmetic against a rolling-window aggregate — the per-row deviation from a trailing average, computed without a self-join.
