Next Order Amount per Customer — LAG and LEAD in SQL

The problem

Brightlane's CRM team is building a forward-looking sequence view of customer orders. Every order should appear alongside the amount that customer spent on their immediately following purchase.

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

Assumptions:

A customer's next order is the order with the smallest ordered_at strictly after the current row's ordered_at, restricted to that customer.
For a customer's most recent order — where the customer has no following order on record — the next-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 next_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,
  LEAD(total_amount) OVER (
    PARTITION BY
      customer_id
    ORDER BY
      ordered_at
  ) AS next_order_amount
FROM
  orders
ORDER BY
  customer_id,
  ordered_at

The shape

LEAD(total_amount) reaches one row forward inside the same customer's time-ordered orders and returns that future order's amount on the current row. The sequence view shows each order next to what the same customer was going to spend on their very next purchase.

Clause by clause

SELECT id, customer_id, total_amount, LEAD(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS next_order_amount returns the order's identifying columns and the customer's next-order amount. PARTITION BY customer_id confines the lookup to that customer's own history; ORDER BY ordered_at establishes the chronological order; LEAD advances exactly one position from the current row.
FROM orders reads every order in the system.
ORDER BY customer_id, ordered_at sorts the result for display.

Why `LEAD` and not `LAG`

LAG looks backward; LEAD looks forward. The CRM team needs the forward-looking view, so LEAD is the right direction. The two functions are mirrors of each other: LEAD(col) on row N returns the same value LAG(col) would return on row N+1. The choice is whether the analysis is anchored to a past reference point or a future one.

The trap

The last order in each customer's history has no following row, so LEAD returns NULL there. This is the symmetric counterpart of the first-row NULL from LAG. The NULL is meaningful; it marks the current end of the customer's recorded history. It does not mean the customer has churned, only that no later order exists in the data yet.

You practiced LEAD(column) OVER (PARTITION BY ... ORDER BY ...) — pull the next record's value into the current record, the symmetric counterpart of LAG.

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

The shape

Clause by clause

Why `LEAD` and not `LAG`

The trap

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

The shape

Clause by clause

Why LEAD and not LAG

The trap

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

Why `LEAD` and not `LAG`