Brightlane's CRM team needs every customer's most recent order for outreach planning. Some customers have placed multiple orders on the same date, so the team needs a deterministic tiebreaker — when order dates tie within a customer, the order with the larger order ID wins.
Write a query to return one row per customer with at least one order, showing that customer's ID, their most recent order's ID, the order date, and the order amount. Sort the final result by customer_id ascending.
Assumptions:
- A customer's most recent order is the order with the largest
ordered_atfor thatcustomer_id. When two orders for the same customer share the sameordered_at, the order with the largeridwins. - Customers with no orders on record do not appear in the result.
- The final result is sorted by
customer_idascending.
Output:
- One row per customer with at least one order, with columns
customer_id,order_id,ordered_at, andtotal_amount. Sorted bycustomer_id.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT DISTINCT
ON (customer_id) customer_id,
id AS order_id,
ordered_at,
total_amount
FROM
orders
ORDER BY
customer_id,
ordered_at DESC,
id DESCThe shape
Two orders for the same customer can share the exact same ordered_at. When that happens, DISTINCT ON (customer_id) still picks exactly one of them, but which one is undefined unless the ORDER BY carries a tiebreaker. ORDER BY customer_id, ordered_at DESC, id DESC adds that tiebreaker as a third sort key: when two orders tie on date, the one with the larger id sorts first and wins the per-customer pick.
Clause by clause
SELECT DISTINCT ON (customer_id) customer_id, id AS order_id, ordered_at, total_amountreturns the four columns the CRM outreach list needs.DISTINCT ON (customer_id)declares one row per distinctcustomer_id.FROM ordersreads the order records. Customers with no orders never enter this row source.ORDER BY customer_id, ordered_at DESC, id DESCsorts the orders by three keys in priority order. The first key,customer_idascending, satisfies theDISTINCT ONrequirement that the leading sort column match the deduplication key, and also gives the final result the customer-ordered shape the prompt asks for. The second key,ordered_at DESC, points each customer's most recent order to the front of their group. The third key,id DESC, only matters when two orders for the same customer share anordered_atvalue — in that case, the largeridsorts first and is the one PostgreSQL keeps.
Why this and not ROW_NUMBER
The tiebreaker carries over directly to the window-function form:
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 DESC, id DESC) AS rn
FROM orders
) ranked
WHERE rn = 1
ORDER BY customer_idBoth forms produce the same deterministic result because both name the same multi-column sort. The lesson is structural: any time the primary sort can tie, the tiebreaker has to live in the ORDER BY itself — not in a downstream filter, not in a post-hoc ORDER BY.
The trap
`DISTINCT ON with a sort that can tie returns a value, but the value is not deterministic. PostgreSQL picks one of the tied rows; which one is not part of the contract. The query runs, the result looks plausible, and on the next run with the same data the picked row can be different. The fix is to extend the ORDER BY with enough sort keys that the row to keep is fully determined. A primary key like id` is the standard last-resort tiebreaker because it is unique by definition, so adding it as the last sort key guarantees the pick is reproducible.
You practiced DISTINCT ON with a tiebreaker column in ORDER BY — adding a secondary sort key after the primary makes the per-group pick deterministic when ties on the primary occur.
