Brightlane's customer analytics team needs each order annotated with two contextual values: how the order's amount compares to that customer's average across their history, and the previous order amount for that customer.
Write a query to return every order's ID, customer ID, order amount, the difference between that order's amount and the customer's average order amount across all their orders, and that same customer's previous order amount.
Assumptions:
- A customer's average is the average of
total_amountacross every order for thatcustomer_id. The same customer-level average appears on every row sharing acustomer_id. - The diff-from-average at each row is the current
total_amountminus the customer's average. - A customer's previous order is the order with the largest
ordered_atstrictly before the current row'sordered_at, restricted to that customer. For a customer's first order, the previous-amount value is missing. - The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount,diff_from_avg, andprev_order_amount. 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
) AS diff_from_avg,
LAG(total_amount) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at
) AS prev_order_amount
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
Two window functions share the same partition (PARTITION BY customer_id) but answer different questions on each row. AVG(total_amount) OVER (PARTITION BY customer_id) computes the customer's lifetime average and duplicates it onto every row; LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) reaches back one chronological step. Both annotations land on the same row without a self-join.
Clause by clause
SELECT id, customer_id, total_amount, total_amount - AVG(total_amount) OVER (PARTITION BY customer_id) AS diff_from_avg, LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS prev_order_amountreturns the order's identifying columns plus two derived annotations. TheAVGwindow has noORDER BYbecause an average over the whole partition does not depend on row order; theLAGwindow does haveORDER BY ordered_atbecause "previous" only means something against a defined sequence.FROM ordersreads every order.ORDER BY customer_id, ordered_atsorts the printed output chronologically within each customer.
Why two separate OVER clauses and not one shared window
The two functions need different window definitions. AVG needs the unordered partition; adding ORDER BY would not change its result here (a partition-wide average is invariant to row order), but LAG strictly requires ORDER BY to know which row is previous. Writing the two OVER clauses out independently makes each function's dependencies explicit. A shared WINDOW alias could deduplicate the PARTITION BY customer_id text but would not change semantics.
The trap
The two window functions both partition by customer_id, which can read as a single shared window. They are not the same window. AVG OVER (PARTITION BY customer_id) and LAG OVER (PARTITION BY customer_id ORDER BY ordered_at) differ in whether ordering is part of the window definition, and that difference is load-bearing. Adding ORDER BY ordered_at to the AVG window would not change its result on this query, but on a frame-sensitive aggregate it absolutely would; an ordered window introduces an implicit running frame, and the average would become a running average instead of a partition average. The cleanest rule when authoring a query with multiple windows is: write each window's clauses to match exactly what that function needs, and do not share unless the sharing is provably semantics-preserving.
You practiced two window functions on the same partition with different roles — AVG OVER (PARTITION BY ...) for a per-customer summary and LAG OVER (PARTITION BY ... ORDER BY ...) for a chronological lookup, both attached to the same row.
