First Order Status per Customer — FIRST_VALUE and LAST

The problem

Brightlane's retention team wants to understand the experience new customers had on their very first order.

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

Assumptions:

The orders table has one row per order with an id, a customer_id, a status, 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 status 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, status, and first_order_status. 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,
  status,
  FIRST_VALUE(status) OVER (
    PARTITION BY
      customer_id
    ORDER BY
      ordered_at
  ) AS first_order_status
FROM
  orders
ORDER BY
  customer_id,
  ordered_at

The shape

FIRST_VALUE works on any column type, not just numerics. FIRST_VALUE(status) returns the text value that sits at position 1 of the ordered partition and copies it onto every row sharing that customer_id. Each order ends up annotated with the status the same customer's first order ended in.

Clause by clause

SELECT id, customer_id, status, FIRST_VALUE(status) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS first_order_status returns each order's identifying columns plus the status of that customer's first order. The window's PARTITION BY customer_id groups the customer's orders together; ORDER BY ordered_at sequences them chronologically; FIRST_VALUE pulls back the value at position 1.
FROM orders reads every order.
ORDER BY customer_id, ordered_at sorts the printed output chronologically within each customer.

The trap

The expression being passed to FIRST_VALUE decides the result type. FIRST_VALUE(status) returns text because status is text; FIRST_VALUE(total_amount) would return numeric. The function does not coerce, summarise, or aggregate the value at position 1; it returns it exactly as stored. Any analytic operation that needs a non-text first value has to be applied either before the lookup (transform the column inside the function call) or after (transform the returned value in the outer query).

You practiced FIRST_VALUE over a non-numeric column — the function returns whatever type sits at the first position, including text values.

Return every order's ID, customer ID, status, and the status of that same customer's 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.