Latest Two Orders per Customer
Hard · Joins
A LATERAL subquery can use columns of the tables before it, so it runs once per row: FROM customers c LEFT JOIN LATERAL (SELECT ... WHERE customer_id = c.id ... LIMIT 2) o ON true. That makes "top N per group" easy.
For every customer, return their 2 most recent orders that are not 'cancelled' (newest ordered_at first; if two orders have the same time, the higher id counts as newer). Customers with no such orders still appear once, with NULLs.
Return customer (the name), order_id, ordered_at and amount, sorted by customer, then ordered_at descending, then order_id descending.