-- 31_lateral_join_top_tx_per_acct.sql
-- Banking Pattern Pack — PostgreSQL
-- Purpose: LATERAL top‑N transactions per account.
-- Context: Retail bank example with customers, accounts, transactions, cards, loans.
-- Date: 2025-08-18
-- Notes:
--   * Safe to read; run on a development DB.
--   * Requires: CREATE privileges; some files may need superuser for extensions.
--   * Search path set to 'bank' schema for clarity.
SET search_path = bank, public;

-- LATERAL: top transaction per account
SELECT a.account_id, t.*
FROM bank.accounts a
LEFT JOIN LATERAL (
  SELECT * FROM bank.transactions tx WHERE tx.account_id = a.account_id ORDER BY tx.created_at DESC LIMIT 1
) t ON true;
