Latest Delivered Order per Customer

The problem

Brightlane's account management team is reviewing every customer's most recent completed purchase. Pending and cancelled orders are excluded.

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

Assumptions:

A delivered order has status = 'delivered'. Only delivered orders are eligible.
A customer's most recent delivered order is the delivered order with the largest ordered_at for that customer_id.
Customers with no delivered 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 delivered 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
WHERE
  status = 'delivered'
ORDER BY
  customer_id,
  ordered_at DESC

The shape

The filter runs before the per-group pick. WHERE status = 'delivered' narrows the order pool to delivered orders only, and then DISTINCT ON (customer_id) with ORDER BY customer_id, ordered_at DESC picks the most recent surviving order for each customer. Customers whose only orders are pending or cancelled drop out at the WHERE step and never reach the per-customer pick.

Clause by clause

SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, ordered_at, total_amount returns the four columns the account-management review needs. DISTINCT ON (customer_id) declares one row per distinct customer_id.
FROM orders reads the order records.
WHERE status = 'delivered' keeps only the delivered orders. Every row downstream is already known to be a delivered order, so the per-customer pick operates on that restricted pool.
ORDER BY customer_id, ordered_at DESC sorts the surviving rows so each customer's most recent delivered order sits first in their group. PostgreSQL keeps the first row in each customer_id group.

Why this and not filter after the pick

You might be tempted to do DISTINCT ON first and then check the status, but that does not produce the right answer. If a customer's most recent order is cancelled and their most recent delivered order is older, the DISTINCT ON step would pick the cancelled one and the filter would then throw the row away — the older delivered order never gets a chance. Filtering before the pick is the only way to get each customer's most recent delivered order.

The WHERE runs before ORDER BY and before the per-group pick. That sequencing is what makes filter-then-pick correct: only delivered orders are sorted, and the most recent of those wins.

You practiced DISTINCT ON after a WHERE restriction — the restriction runs first; the per-group pick operates on only the surviving records.

Return one row per customer with at least one delivered order, showing that customer's ID, the ID of their most recent delivered 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 filter after the pick

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