Window Function Running Total Balance

-- 按账户分区、按时间排序的累计余额
WITH ledger AS (
  SELECT
    account_id,
    occurred_at,
    amount_cents,
    SUM(amount_cents) OVER (
      PARTITION BY account_id
      ORDER BY occurred_at, id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_balance
  FROM transactions
  WHERE status = 'settled'
)
SELECT
  account_id,
  occurred_at,
  amount_cents,
  running_balance,
  LAG(running_balance, 1, 0) OVER (
    PARTITION BY account_id ORDER BY occurred_at, id
  ) AS previous_balance
FROM ledger
WHERE running_balance < 0  -- 首次透支的时刻一目了然
ORDER BY account_id, occurred_at;

-- 配套索引：窗口的 PARTITION + ORDER 键
-- CREATE INDEX idx_tx_account_time
--   ON transactions (account_id, occurred_at, id);