First Order Amount per Customer — FIRST_VALUE and LAST

The problem

Brightlane's CRM team is building a customer order history view. Every order should be annotated with that customer's initial purchase amount for easy reference.

Write a query to return every order's ID, customer ID, order amount, and the amount of that same customer's very first order chronologically.

Assumptions:

The orders table has one row per order with an id, a customer_id, a total_amount, and an ordered_at timestamp.
A customer's first order is the order with the smallest ordered_at for that customer_id. The same first-order amount appears on every row sharing a customer_id.
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 first_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,
  FIRST_VALUE(total_amount) OVER (
    PARTITION BY
      customer_id
    ORDER BY
      ordered_at
  ) AS first_order_amount
FROM
  orders
ORDER BY
  customer_id,
  ordered_at

The shape

FIRST_VALUE(total_amount) returns the value at position 1 of the ordered partition, and replicates that single value onto every row in the partition. Each order ends up sitting next to the dollar amount of that same customer's earliest order, with no join and no subquery.

Clause by clause

SELECT id, customer_id, total_amount, FIRST_VALUE(total_amount) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS first_order_amount returns each order's identifying columns plus the customer's first-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. FIRST_VALUE then reaches into position 1 of that ordered sequence and pulls back the total_amount from there.
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 is separate from the window's sort; it controls display, not the lookup.

The trap

The first-order amount on the row that is the customer's first order equals that row's own total_amount, because position 1 of the partition is that very row. This is correct, not a bug. A reader who expects NULL or a fallback on the anchor row has imported the LAG mental model. FIRST_VALUE always has a value to return for a non-empty partition; there is no "no prior row" case to handle here.

You practiced FIRST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...) — pull the value at position 1 of the ordered partition and replicate it onto every record in that partition.

Return every order's ID, customer ID, order amount, and the amount of that same customer's very first order chronologically

The shape

Clause by clause

The trap

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