LATERAL, for a top-N per group without a window function

A correlated derived table can reference columns from the row it is joined against, which expresses top-N-per-group directly.

SELECT c.id, c.name, o.id AS order_id, o.total_cents
FROM customers c
JOIN LATERAL (
  SELECT id, total_cents FROM orders
  WHERE customer_id = c.id
  ORDER BY placed_at DESC
  LIMIT 3
) o ON TRUE
WHERE c.tier = 'gold';

A window function with ROW_NUMBER reads more clearly and computes the numbering for every row before filtering, which on a large table is a great deal of wasted work. The lateral form stops at three per customer, and the difference on a table with a few very active customers is substantial. It is one of the few places where the less obvious query is also the faster one.