Postgres LATERAL join for top-N per group — TIL

LATERAL lets the subquery reference columns from the outer query. Handy for “top-N per group”:

SELECT u.id, recent.*
FROM users u
CROSS JOIN LATERAL (
  SELECT created_at, amount
  FROM payments p
  WHERE p.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) AS recent;

Each users row gets its 3 most recent payments.