Brightlane's revenue analytics team wants to quantify how much each order differs from that customer's very first purchase.
Write a query to return every order's ID, customer ID, order amount, and the difference between that order's amount and the same customer's first order amount chronologically.
Assumptions:
- A customer's first order is the order with the smallest
ordered_atfor thatcustomer_id. - The change-from-first at each row is the current
total_amountminus the customer's first-ordertotal_amount. On the row that is itself the customer's first order, the change-from-first equals0. - The final result is sorted by
customer_idascending, then byordered_atascending.
Output:
- One row per order, with columns
id,customer_id,total_amount, andchange_from_first. 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 - FIRST_VALUE(total_amount) OVER (
PARTITION BY
customer_id
ORDER BY
ordered_at
) AS change_from_first
FROM
orders
ORDER BY
customer_id,
ordered_atThe shape
The current row's total_amount minus FIRST_VALUE(total_amount) gives a per-row deviation from the customer's anchor order. The window function does the lookup; the arithmetic does the comparison. The result lives on the same row as the current order, with no self-join.
Clause by clause
SELECT id, customer_id, total_amount, total_amount - FIRST_VALUE(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS change_from_firstreturns each order's identifying columns plus the deviation.FIRST_VALUEresolves to the customer's first-order amount on every row; the subtraction then computescurrent minus firstrow by row.FROM ordersreads every order.ORDER BY customer_id, ordered_atsorts the printed result chronologically within each customer so the deltas read in time order.
Why this and not a self-join on the first order per customer
A self-join could compute the same number: aggregate to find each customer's first order, then join that back to every order, then subtract. FIRST_VALUE collapses that pattern into a single window expression. The aggregate-and-join path requires a separate scan over the table to build the per-customer first-order list and a join key to reattach it. The window function does both jobs in one pass over the partition, and the SQL stays inside a single query block. On a problem this shape, the window form is the canonical answer.
The trap
On the row that is the customer's first order, FIRST_VALUE returns that same row's total_amount, so the subtraction returns 0. This is correct, not a bug. The anchor row's "change from itself" is genuinely zero. A reader who imported the LAG mental model might expect NULL on the anchor row, because LAG has no prior row to return. FIRST_VALUE always has a value at position 1 of a non-empty partition, and the subtraction sees a real number on every row.
You practiced inline arithmetic combining a per-row column with FIRST_VALUE — each record's deviation from its partition's anchor value, computed without a self-join.
