Earliest Order per Customer — DISTINCT ON in SQL

The problem

Brightlane's acquisition team is reviewing every customer's very first purchase to understand initial buying patterns.

Write a query to return one row per customer with at least one order, showing that customer's ID, the ID of their earliest order, when it was placed, and the order amount. Sort the final result by customer_id ascending.

Assumptions:

A customer's earliest order is the order with the smallest ordered_at for that customer_id.
Customers with no orders on record do not appear in the result.
The final result is sorted by customer_id ascending.

Output:

One row per customer with at least one order, with columns customer_id, order_id, ordered_at, and total_amount. Sorted by customer_id.

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 DISTINCT
  ON (customer_id) customer_id,
  id AS order_id,
  ordered_at,
  total_amount
FROM
  orders
ORDER BY
  customer_id,
  ordered_at

The shape

The earliest order per customer is the same DISTINCT ON pattern as latest-per-group, with the secondary sort pointing the other way. ORDER BY customer_id, ordered_at (ascending, the default) puts each customer's oldest order first, so PostgreSQL keeps the oldest row in each customer_id group.

Clause by clause

SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, ordered_at, total_amount returns the four columns the acquisition review needs. The DISTINCT ON (customer_id) part declares the deduplication key: one row per distinct customer_id. The id AS order_id alias names the order identifier column.
FROM orders reads the order records. Customers with no orders never enter this row source, so they cannot appear in the result.
ORDER BY customer_id, ordered_at sorts the orders so each customer's earliest order sits first in their group. PostgreSQL keeps the first row for each new customer_id value, which is the oldest order for that customer. The leading customer_id ascending also gives the final result the customer-ordered shape the prompt asks for.

Why this and not `ROW_NUMBER`

The same first-order-per-customer pick is reachable with a window function:

SELECT customer_id, order_id, ordered_at, total_amount
FROM (
  SELECT customer_id, id AS order_id, ordered_at, total_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at) AS rn
  FROM orders
) ranked
WHERE rn = 1
ORDER BY customer_id

Both return the same rows. DISTINCT ON swaps from latest to earliest by flipping a single keyword — drop DESC from the secondary sort, the rest of the query is unchanged. That is the load-bearing detail: the direction of the secondary sort controls whether the kept row is the freshest or the oldest in each group.

You practiced DISTINCT ON with ascending secondary sort — same shape as latest-per-group, but the secondary sort points the other way so the earliest record wins.

Return one row per customer with at least one order, showing that customer's ID, the ID of their earliest order, when it was placed, and the order amount. Sort the final result by `customer_id` ascending

The shape

Clause by clause

Why this and not `ROW_NUMBER`

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

The shape

Clause by clause

Why this and not ROW_NUMBER

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

Why this and not `ROW_NUMBER`