LAMP Stack Development answered top-N-per-group questions with ROW_NUMBER(), which numbers every row before filtering. LATERAL with ORDER BY ... LIMIT asks once per customer, and an index answers each with a short read:
CREATE INDEX IF NOT EXISTS orders_customer_ts ON orders (customer_id, order_ts DESC);
\timing on
SELECT count(*), sum(last.total) FROM customers c
CROSS JOIN LATERAL (SELECT o.order_id, o.total FROM orders o
WHERE o.customer_id = c.customer_id
ORDER BY o.order_ts DESC LIMIT 2) AS last;
SELECT count(*), sum(total) FROM (
SELECT total, row_number() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) AS rn
FROM orders) ranked
WHERE rn <= 2;Output
count | sum -------+----------- 10000 | 342718.88 Time: 23.166 ms count | sum -------+----------- 10000 | 342718.88 Time: 73.244 ms
Both return the same 10,000 orders; the lateral form was 3.2 to 4.6 times faster over five runs on the shared host, reading 10,000 index entries instead of numbering 100,000 rows. Without a suitable index each probe becomes a scan, so prefer ROW_NUMBER() when N is large or groups are few.