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'
```

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