On-chain data and SQL · Lesson 5 of 9
The account model and reconstructing balances
13 min read
Ethereum and the many chains that copy its design keep a running balance for every address instead of a pile of discrete coins. That changes what can be traced, what a "transaction" means and who the real actor is: with smart accounts, the address that signs, the address that pays gas and the address in the from field are often three different parties, and confusing them attributes activity to the wrong one. It also shapes a common casework question, "how much did this address hold on that date, and how did it get there?", which a careful examiner answers twice: by deriving the balance from history and by reading it from chain state, then explaining any difference.
State, not coins
An account-model chain stores a world state: a mapping from each 20-byte address to an account record. On Ethereum that record has four fields:
| Field | Meaning | Analyst use |
|---|---|---|
nonce | Transactions sent (EOA) or contracts created (contract) | Activity count, ordering, fresh-address checks |
balance | Native currency, in wei | Holdings at a block |
codeHash | Hash of the account's code (empty for a plain EOA) | Telling EOAs, contracts and delegated EOAs apart |
storageRoot | Root of the account's key-value storage | Where token balances and contract data live |
Every block commits to the resulting state through the stateRoot in its header. Balances are read with eth_getBalance(address, block), contract storage through eth_call (for example a token's balanceOf) or eth_getStorageAt.
| Question | UTXO chains | Account chains |
|---|---|---|
| Unit of value | Discrete outputs, spent whole | One balance per address |
| Change | A new output, often to a new address | None: the remainder stays in the balance |
| Linking a payment out to a payment in | Exact per output; ambiguous inside a multi-input transaction | Never exact: all deposits merge |
| Address reuse | Discouraged | Normal; one address for years |
| Replay protection | Each output can be spent once | Each transaction must use the next nonce |
Once 10 ETH arrives at an address that already holds 50 ETH, nothing in the data says which ETH left next. Any attribution of an outgoing payment to a particular deposit is a tracing convention chosen by the analyst, not a recorded fact (Lesson 3.3 covers the methods).
EOAs, contracts and delegated EOAs
An externally owned account (EOA) is controlled by a private key and is the only kind of account that can originate a transaction. A contract account has code and storage and acts only when called. eth_getCode(address, block) returns 0x for an EOA and the deployed bytecode for a contract. Three pitfalls make this check less simple than it looks:
- Timing. Code exists only from the block in which the contract was created. Addresses can be computed before deployment (CREATE2 makes this deliberate), so an address with no code at block N may be a contract at N+1. Check code at the block that matters.
- Destruction. Before the Dencun upgrade (March 2024),
SELFDESTRUCTdeleted a contract's code. Since EIP-6780 it does so only when called in the same transaction that created the contract; otherwise it just sends the balance away. - Delegation. Since the Pectra upgrade (May 2025), an EOA can carry the 23-byte EIP-7702 delegation designator
0xef0100followed by a delegate address. A naive "has code, so it is a contract" rule misclassifies it: the account is still controlled by its private key.
A robust classifier therefore returns three classes (EOA, contract, delegated EOA) as of a given block.
The nonce is a gift to analysts. For an EOA it equals the number of transactions sent, so nonce 0 means the address has never sent anything, and gaps or bursts reveal operational patterns (Lesson 4.3). Two caveats: each EIP-7702 authorization an EOA signs also increments its nonce, and a contract's nonce counts contract creations, starting at 1 under EIP-161. Contract addresses derive from the deployer: CREATE uses the deployer's address and nonce, CREATE2 the deployer, a salt and the hash of the creation code, which is why deployer links are strong evidence.
Where history lives
A state query answers "what did this address hold at block N?" History answers "how did it get there?", and it is spread across several datasets:
- top-level transactions: value sent, plus gas paid by the sender;
- traces of internal calls: value moved by contracts;
- logs: token transfers and other events;
- balance changes that are not transactions: priority fees credited to the block's fee recipient, consensus-layer withdrawals credited directly to addresses since April 2023 (EIP-4895: no gas, no code execution, no transaction hash), genesis allocations and, before September 2022, mining rewards.
Full nodes keep recent state only, so state at old blocks needs an archive node or a provider that offers one (Lesson 2.1).
Internal transactions
In the protocol, a transaction is only the signed, top-level message. When a contract forwards ETH during execution, that is a message call: no hash, no receipt and no log unless the code emits one. Explorers call these "internal transactions," a convenient but misleading label. Suppose a user's transaction sends 0 ETH to a wallet contract 0x7c3E…12, which during execution calls 0xD4e5…3a with value 0xde0b6b3a7640000, which is 10^18 wei or 1 ETH. The transaction list for 0xD4e5…3a shows nothing, yet its balance rose by 1 ETH. Only a trace (Lesson 2.4) explains the change. Reverted subcalls move nothing and must be excluded.
An ERC-20 transfer is different again: a change in the token contract's storage announced by a Transfer event, not an internal transaction (Lesson 3.5).
Smart accounts: who signs, who pays, who acts
A smart account is a wallet implemented as a contract, with rules such as multiple signers, spending limits, recovery or sponsored gas.
Contract wallets (multisig wallets are the classic case) hold funds at a contract address. Owners sign off chain and one of them, or a relayer, submits the transaction, so the EOA in from may just be the last signer.
ERC-4337 adds account abstraction without protocol changes. Users sign a UserOperation (fields include sender, nonce, callData, gas limits, paymaster and signature) and send it to a separate mempool. A bundler packs many of them into one transaction calling handleOps on a singleton EntryPoint contract, which validates and executes each operation and pays the bundler from the account's deposit or a paymaster's. For each operation it emits:
UserOperationEvent(bytes32 indexed userOpHash, address indexed sender, address indexed paymaster, uint256 nonce, bool success, uint256 actualGasCost, uint256 actualGasUsed)
The transaction's from is the bundler, one transaction can carry operations from unrelated users, the receipt covers the whole bundle, and the real actor is the event's sender. Using the generic schema of Lesson 2.7:
-- One row per ERC-4337 UserOperation, with bundler, smart account and paymaster
SELECT l.block_number,
l.transaction_hash,
t.from_address AS bundler,
'0x' || SUBSTR(l.topic2, 27, 40) AS smart_account, -- indexed sender
'0x' || SUBSTR(l.topic3, 27, 40) AS paymaster -- zero address = none
FROM logs AS l
JOIN transactions AS t ON t.hash = l.transaction_hash
WHERE l.topic0 = '0x49628fd1471006c1482da88028e9ce4dbb080b815c9b0344d39e5a8e6ec1419f'
AND l.contract_address IN (SELECT address FROM entrypoint_contracts);
topic0 is the Keccak-256 hash of the event signature, and SUBSTR(topic, 27, 40) extracts an indexed address from its 32-byte, zero-padded topic (Lesson 2.9); some engines call the function SUBSTRING, and datasets that store topics as binary need byte slicing instead. Restricting to known EntryPoint addresses (one per version, kept in a small case table) avoids matching look-alike events from other contracts.
EIP-7702 lets an existing EOA adopt smart-account behavior. A type 4 transaction carries authorizations [chain_id, address, nonce, y_parity, r, s]; each sets the signer's code to the delegation designator, which persists until replaced or cleared, and an authorization with chain_id 0 is valid on every chain. The address keeps its history and key but can now batch calls or have its gas paid by others. Delegation targets are evidence too: a public on-chain dashboard published by the trading firm Wintermute in June 2025 found that over 80% of early delegations pointed to copies of one "sweeper" contract that drains wallets whose keys have leaked.
| Pattern | Who authorizes | Transaction from (pays gas) | Where to find the real actor |
|---|---|---|---|
| Plain EOA transaction | EOA key | The same EOA | from |
| Contract wallet | Owners' signatures in call data | An owner or a relayer | to (the wallet) and its events |
| Meta-transaction (ERC-2771) | User's signature | A relayer via a trusted forwarder | Decoded call data |
| ERC-4337 | Smart account's validation rules | Bundler, reimbursed by account or paymaster | UserOperationEvent.sender |
| EIP-7702 sponsored | EOA's authorization and signed payload | A sponsor | The delegated EOA called |
Never equate the gas payer with the actor until the pattern is identified. Gas funding remains useful evidence, but under sponsorship it may point to a service rather than an owner.
Two routes to a balance
| Route | How | Strengths | Weaknesses |
|---|---|---|---|
| Derive from history | Add up every credit and debit up to a block | Explains each movement; works in a warehouse | Easy to miss a category of movement |
| Read from state | Ask a node for the balance at a block | Authoritative for that chain and block | No explanation; old blocks need archive state |
The derived figure goes into the report because it tells the story; the state figure is the check. When they agree, the history is complete. When they differ, something is missing, and the size of the gap usually says what.
UTXO balances
On a UTXO chain the balance of a set of scripts at height H is the sum of outputs to them created at or before H and not spent at or before H (Lesson 2.3). For one address:
| Outpoint | Created at | Amount (BTC) | Spent at |
|---|---|---|---|
4f3a…c21e:1 | 880,100 | 0.4000 | 880,900 |
a91c…3b07:0 | 880,450 | 1.2500 | not spent |
77d2…e4a0:3 | 881,020 | 0.0800 | 885,300 |
c0b5…91f2:0 | 884,700 | 0.3000 | not spent |
At 882,000 the unspent outputs are a91c…3b07:0 and 77d2…e4a0:3: 1.33 BTC. At 886,000 they are a91c…3b07:0 and c0b5…91f2:0: 1.55 BTC. Received (2.03) minus sent (0.48) gives the same 1.55, a useful internal check. A wallet is not an address: if output descriptors or an extended public key are known (for example, supplied by the owner in a dispute), derive every address, including change addresses, before summing. Mempool transactions are not balances; coinbase outputs under 100 blocks old exist but cannot be spent yet. Bitcoin Core's scantxoutset sees only the current UTXO set, so historical checks need an indexer or a second, independently built dataset.
Native balances on account chains
balance(N) = value received in successful top-level transactions
+ value received in successful internal calls (traces)
+ consensus-layer withdrawals and fee-recipient rewards
- value sent in successful top-level transactions and internal calls
- gas fees paid on every transaction sent, successful or failed
Genesis allocations and pre-Merge mining rewards add terms for older addresses. A worked example for 0x6F2a…c19E at block N:
| Item | Source | Amount (ETH) |
|---|---|---|
| Deposit from an exchange | Transaction | +5.000000 |
| Payout from a contract | Trace (internal call) | +1.000000 |
| Validator withdrawals | Block withdrawals list | +0.032000 |
| Payment sent | Transaction | −2.500000 |
| Gas on that payment: 21,000 × 20 gwei | Receipt | −0.000420 |
| Failed transaction: value not sent | Receipt status = 0 | 0 |
| Gas on the failed transaction: 70,000 × 30 gwei | Receipt | −0.002100 |
| Derived balance | 3.529480 |
If eth_getBalance(0x6F2a…c19E, N) returns 3.529480 ETH, the history is complete. Failures leave fingerprints: ignoring traces under-states by exactly the internal credit (1.0); counting the failed transaction's value under-states by that value; forgetting its gas over-states by 0.0021; missing withdrawals leave a gap of small, regular amounts. Gas is gas used × effective gas price from the receipt, never the gas limit, plus blob fees for type 3 transactions.
Token balances: events versus balanceOf
Token balances live in the token contract's storage. They can be derived by summing Transfer events or read with balanceOf(holder) through eth_call at block N:
-- Token balances at block 21000000, derived from Transfer events
WITH moves AS (
SELECT to_address AS holder, amount_raw AS delta
FROM token_transfers
WHERE token_address = '0x5d3a…7c10' AND block_number <= 21000000
UNION ALL
SELECT from_address AS holder, -amount_raw AS delta
FROM token_transfers
WHERE token_address = '0x5d3a…7c10' AND block_number <= 21000000
)
SELECT holder, SUM(delta) / POWER(10, 6) AS balance -- token has 6 decimals
FROM moves
GROUP BY holder
HAVING SUM(delta) <> 0;
Each transfer becomes a credit and a debit. Mints are transfers from the zero address, so the zero address ends with a negative balance equal to everything minted minus everything burned to it: a built-in supply check. Keep raw integers in a wide decimal type until the final division.
Events and balanceOf disagree for known reasons: rebasing tokens change every balance through a global multiplier with no per-holder event; interest-bearing tokens compute balanceOf from a growing index; fee-on-transfer tokens credit the receiver less than the sender sends, and some emit the gross amount; and some older or issuer-controlled contracts mint without Transfer or emit a custom event when freezing and burning. For these, state is the truth and events tell only the story of explicit transfers.
Snapshots and reconciliation
A balance "on March 31" is ambiguous until tied to a block: take the last block whose timestamp is at or before the cutoff, in UTC, and record its height and hash. Under EIP-1898, eth_getBalance and eth_call accept a block hash, which guarantees the answer comes from that exact block even if a reorganization later replaces the block at that height. For multi-chain snapshots, each chain has its own block for the same instant (Bitcoin's roughly 10-minute blocks give coarser steps than Ethereum's 12-second slots, and a rollup block may reflect deposits its L1 has only just included); state the block chosen on each.
A reconstruction is not finished until checked: compare derived balances with state (all addresses in scope, or a sample at several blocks); check that derived token balances, excluding the zero address, equal totalSupply() at the same block; treat any negative running balance as a missing credit; check boundaries (zero before the first activity, and what a known sweep left); and set a tolerance in advance, documenting the cause of every residual.
| Symptom | Likely cause |
|---|---|
| Derived balance too low by amounts matching contract payouts | Missing internal calls from traces |
| Derived balance too high by small amounts | Gas on failed transactions not deducted |
| Small regular unexplained credits | Consensus-layer withdrawals or fee-recipient rewards |
Token holders drift from balanceOf daily | Rebasing or interest-bearing token |
Sum of balances differs from totalSupply() | Mints or burns without Transfer events |
Exam focus: expect scenario questions on who acted and on why a balance does not reconcile: the four account fields and the meaning of the nonce; why pooled balances make coin-level tracing a convention; classifying EOAs, contracts and EIP-7702 delegated EOAs at a block (0xef0100, EIP-6780); internal transactions as message calls visible only in traces; withdrawals and fee rewards as non-transaction credits; the ERC-4337 roles andUserOperationEvent.sender; derived versus state balances; the native-balance components, especially gas on failed transactions; token balances from events and the zero-address supply check; tokens that break event sums; pinning snapshots by height and hash (EIP-1898); what each kind of gap suggests.
Practice questions
Check your understanding. These questions do not count towards the exam.
Sign in to save your progress and continue where you left off. It is free. Sign in