Changes in balances in ERC20 contracts on Ethereum
Schema
Column Name | Data Type |
---|---|
txn_hash | string |
block_number | bigDecimal |
block_timestamp | bigDecimal |
global_counter | bigDecimal |
token_address | string |
token_name | string |
token_symbol | string |
token_decimals | bigDecimal |
account_address | string |
value_before | bigDecimal |
value_after | bigDecimal |
Source
This table was built in sim's Ethereum ERC20 Balance Changes canvas.
Usage notes
It tracks balance values in the uint256 format of contract storage. For the presentation users expect, divide values here by 10 to the power of d, where d
is the decimals()
of the given contract.
Sample queries
1. Current balances of an address
To achieve this, we filter to the balance changes for a specific address and find the balance change record with the highest global counter for each token. We then do an inner join to select for only those most recent records.
WITH latest_global_counter AS (
SELECT token_address,
MAX(CAST(global_counter as double)) AS latest_global_counter
FROM @sim.ethereum_erc20_balance_changes
WHERE account_address = '0x48d004a6c175db331e99beaf64423b3098357ae7'
GROUP BY token_address
)
SELECT b.token_address,
b.value_after AS value,
b.block_number AS block_number
FROM @sim.ethereum_erc20_balance_changes b
INNER JOIN latest_global_counter lgc ON b.token_address = lgc.token_address
AND b.global_counter = lgc.latest_global_counter
WHERE b.account_address = '0x48d004a6c175db331e99beaf64423b3098357ae7'
AND b.value_after > 0
LIMIT 50
2. Top holders of a token
This is a very heavy query that will only work in special cases:
- It's heavy because we need to scan for the most recent balance change for each holder and then sort the results. For tokens with many holders, this is a lot.
- It will only work for tokens with fewer than 100K unique holders, including accounts that have held the token in the past but now have zero balances. The reason for this is that our DB solution, Pinot, has a 100K max on the group by. Beware: The query may appear to succeed even though it disregards all but 100K accounts.
- To make sure you're not running into this issue in your queries, you can perform a distinct count on the columns you intend to group on:
SELECT distinctcount(account_address) FROM @sim.ethereum_erc20_balance_changes WHERE token_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
- To make sure you're not running into this issue in your queries, you can perform a distinct count on the columns you intend to group on:
Here's a version that does work because it's a token with fewer holders (ConstitutionDAO):
WITH latest_global_counter AS (
SELECT account_address,
MAX(CAST(global_counter as double)) AS latest_global_counter
FROM @sim.ethereum_erc20_balance_changes
WHERE token_address = '0x7a58c0be72be218b41c608b7fe7c5bb630736c71'
GROUP BY account_address
)
SELECT b.account_address,
b.value_after AS value,
b.block_number AS block_number
FROM @sim.ethereum_erc20_balance_changes b
INNER JOIN latest_global_counter lgc ON b.account_address = lgc.account_address
AND b.global_counter = lgc.latest_global_counter
WHERE b.token_address = '0x7a58c0be72be218b41c608b7fe7c5bb630736c71'
ORDER BY value desc
LIMIT 5
If you want to perform queries like these, reach out to us on Telegram and let's talk about it. We have some solutions in mind.
Modifications for historical queries
If you want to get the data either of the above queries at/after a specific block, just add an additional WHERE
filter on block_number
, e.g.,
WITH latest_global_counter AS (
SELECT token_address,
MAX(CAST(global_counter as double)) AS latest_global_counter
FROM @sim.ethereum_erc20_balance_changes
WHERE account_address = '0x48d004a6c175db331e99beaf64423b3098357ae7'
AND block_number <= 18000000
GROUP BY token_address
)
SELECT b.token_address,
b.value_after AS value,
b.block_number AS block_number
FROM @sim.ethereum_erc20_balance_changes b
INNER JOIN latest_global_counter lgc ON b.token_address = lgc.token_address
AND b.global_counter = lgc.latest_global_counter
WHERE b.account_address = '0x48d004a6c175db331e99beaf64423b3098357ae7'
AND block_number <= 18000000