Change from Previous Order — LAG and LEAD in SQL

The problem

Brightlane's revenue analytics team tracks how each customer's spending shifts order-over-order — a positive number on a row means the customer spent more than on their prior order; a negative number means they spent less.

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 immediately preceding order amount.

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.
The amount-change at each row is the current total_amount minus the previous order's total_amount.
For a customer's first order — where no previous order is on record — the amount-change is missing.
The final result is sorted by customer_id ascending, then by ordered_at ascending.

Output:

One row per order, with columns id, customer_id, total_amount, and amount_change. 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,
  total_amount - LAG(total_amount) OVER (
    PARTITION BY
      customer_id
    ORDER BY
      ordered_at
  ) AS amount_change
FROM
  orders
ORDER BY
  customer_id,
  ordered_at

The shape

Subtracting the LAG-retrieved prior amount from the current row's amount produces a per-row delta: positive means the customer spent more than last time, negative means they spent less, and the result lives on the same row as the current order. The window function does the lookup; the subtraction does the comparison.

Clause by clause

SELECT id, customer_id, total_amount, total_amount - LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS amount_change returns each order's identifying columns and the order-over-order delta. The LAG runs first, fetching the customer's previous order amount; the subtraction then computes the difference.
FROM orders reads every order.
ORDER BY customer_id, ordered_at sorts the result so the deltas read in chronological order within each customer.

The trap

The first order in any customer's history produces a NULL delta, because LAG returns NULL there and any arithmetic with a NULL operand is also NULL. The NULL is correct; there is no prior order to compare against, so the delta genuinely does not exist. A reader expecting 0 on those rows would misread "no prior order" as "no change since prior order." If the report needs a numeric 0 on those rows specifically because the consumer cannot handle NULL, LAG accepts a third argument that supplies a default for the no-prior-row case, and the subtraction then sees total_amount - 0 and returns the customer's first-order amount untouched.

You practiced current - LAG(...) for inline period-over-period difference — LAG makes the prior value available on the same row, so the subtraction needs no self-join.

Return every order's ID, customer ID, order amount, and the difference between that order's amount and the same customer's immediately preceding order amount

The shape

Clause by clause

The trap

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