Wallet Balances
Ethereum Wallet Balance tables available to query via SQL, in Preview
Wallet Balance specific tables
Table Name | Description |
|---|---|
A block-level view of all changes to any account's Ether balance | |
eth.recent_wallet_balances | Balances changed in the last 30 minutes, ~128 blocks |
The columns and their schema for each table can be viewed with the describe <table> command. For example:
-- Show the columns available
DESCRIBE eth.wallet_balances;
One of the most fundamental queries to make on any blockchain is the native currency balance of a wallet. The
eth.wallet_balances table contains a block-level view of all Ether balance changes due to gas fees, withdrawals, and committed external and internal (contract) transactions.Getting the balance of a wallet becomes a simple act of querying the absolute balance (in gwei) at the last block that it changed:
WITH ranked_balances AS (
SELECT address, balance_gwei, balance_usd, ROW_NUMBER() OVER (PARTITION BY address ORDER BY block_number DESC) AS rn
FROM eth.wallet_balances
)
SELECT address, balance_gwei, balance_usd
FROM ranked_balances WHERE rn = 1 and address = LOWER('0x28c6c06298d514db089934071355e5743bf21d60')
This dataset contains the
balance_gwei (Ether balance, in gwei) at the block in question, as well as an estimated balance_usd in USD. In cases where the gwei balance is too large to represent efficiently, the full value can be extracted programmatically from the hexadecimal string representation in the balance_hex column.We are indexing a growing accumulation of historical data too:
-- get the net peak-to-peak balance change for a wallet over the last week
SELECT address, max(balance_gwei) - min(balance_gwei) as "last week delta (gwei)", max(balance_usd) - min(balance_usd) as "last week delta (USD)"
FROM eth.wallet_balances
WHERE address = '0x28c6c06298d514db089934071355e5743bf21d60'
AND block_timestamp > UNIX_TIMESTAMP() - 7*24*60*60*1000
GROUP BY address
Additionally, the most recent 30 minute's of wallet balance changes are also cached in a high performance recent table
eth.recent_wallet_balances.The above datasets combine to facilitate rich, performant, historical data access beyond the standard offerings in class. Going forward, we are considering additional SQL representations that would make other common access patterns even easier. Reach out on Discord if you have use cases not covered here.
Query performance can be significantly improved by adding
WHERE clauses to your query on specific indexed columns.Table Name | Indexed Columns |
|---|---|
eth.wallet_balances | block_number block_timestamp address |
eth.recent_wallet_balances | block_number block_timestamp address |
Last modified 23d ago