Previous Order Amount per Customer

The problem

Brightlane's sales operations team is compiling an order history report. Every order should appear alongside the amount that customer spent on their immediately preceding purchase.

Write a query to return every order's ID, customer ID, order amount, and that customer's previous order amount, ordered chronologically within each customer.

Assumptions:

A customer's previous order is the order with the largest ordered_at strictly before the current row's ordered_at, restricted to that customer.
For a customer's first order — where the customer has no preceding order on record — the previous-amount value is missing.
The final result is sorted by customer_id ascending, then by ordered_at ascending within each customer.

Output:

One row per order, with columns id, customer_id, total_amount, and prev_order_amount. Sorted by customer_id, then ordered_at.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  id,
  customer_id,
  total_amount,
  LAG(total_amount) OVER (
    PARTITION BY
      customer_id
    ORDER BY
      ordered_at
  ) AS prev_order_amount
FROM
  orders
ORDER BY
  customer_id,
  ordered_at

The shape

LAG(total_amount) returns the value of total_amount from the previous row inside the same customer's chronologically-ordered orders. Every order ends up sitting next to the dollar amount the same customer spent immediately before, with no join and no subquery.

Clause by clause

SELECT id, customer_id, total_amount, LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS prev_order_amount returns the order's identifying columns and the customer's prior-order amount. PARTITION BY customer_id puts each customer's orders into their own window; ORDER BY ordered_at defines the chronological sequence inside that window; LAG reaches back exactly one position in that ordered sequence.
FROM orders reads every order. No filter; every order is included.
ORDER BY customer_id, ordered_at sorts the printed result so each customer's history reads top to bottom in time order. This outer sort and the window's sort are separate clauses; the outer one controls display, the inner one controls the lookup.

The trap

The first order in any customer's history has no prior row, so LAG returns NULL for that row. The NULL is informative; it marks where the customer's history begins. Any downstream arithmetic on prev_order_amount will silently produce NULL on those first-order rows. If the consumer needs a numeric value on every row, LAG accepts a third argument as a default for the no-prior-row case.

You practiced LAG(column) OVER (PARTITION BY ... ORDER BY ...) — pull the previous record's value into the current record by partition and ordering, eliminating a self-join with a date offset.

Return every order's ID, customer ID, order amount, and that customer's previous order amount, ordered chronologically within each customer

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.