Новости экономики и финансов. Курсы валют, ситуация на биржах, акции, котировки, аналитика: 💫 💫 💫 💫 💫 💫 💫 читайте на сайте

All Ethereum Addresses With Balance

How to Query Balances for all Ethereum Addresses in BigQuery

Recently we announced beta availability of transaction traces (aka internal transactions) in our public Ethereum dataset in BigQuery: https://bigquery.cloud.google.com/table/bigquery-public-data:crypto_ethereum.traces. They allow querying all Ethereum addresses with their balances.

Below is the query that will give you top 10 balances:
The balances table is updated daily. In case you want live balances you can use the below query:

Here is the result, on the day of writing (as csv file):
Here is the high-level overview of the workflow we used to get this result:

  1. Export all traces from Parity node to BigQuery using Ethereum ETL.
  2. Enrich the traces with the status field which accounts for parent trace failures.
  3. Add genesis and daofork traces.
  4. Write an SQL to calculate the balances.

Along the way I had to solve a few interesting challenges, which I will uncover in the following sections.

Calculating Trace Statuses

The traceAddress field of all returned traces, gives the exact location in the call trace [index in root, index in first CALL, index in second CALL, …].

i.e. if the trace is:
then it should look something like:

Parity also provides an error string for each trace in case it failed. Unfortunately it doesn’t account for the top-level trace failures. For example, if the call to C in the example above succeeded, but the top-level call to A failed due to say out-of-gas error, then the trace for the call to C will not return any error with it. This is problematic because any state changes in the call, including Ether transfers, are reverted. We want to filter out such nested calls that look like successes in the traces but actually were reverted due to top-level trace failures.

Your first thought may be to reconstruct the call tree from the flat traces returned by Parity, for each call traverse up the tree and see if any top-level calls failed, set the status for each call accordingly.

Another solution, which I ended up using, is to first query all failed traces, then find all their child traces and mark them as failures. This is easily done in BigQuery with the following SQL: https://github.com/blockchain-etl/ethereum-etl-airflow/blob/master/dags/resources/stages/enrich/sqls/traces.sql

The status field is 0 when the trace succeeded, and 1 when it failed due to any operation that can cause the call itself or any top-level call to revert. This is consistent with the status field in transaction receipts.

Handling Irregular State Changes in DAO Fork

If you simply query balances from the traces returned by Parity, these two addresses will come up at the top:

In reality, the above addresses have only 7 Wei each, on the day of writing. If you google them you will encounter this post about the DAO fork by Vitalik Buterin https://blog.ethereum.org/2016/07/20/hard-fork-completed/. Here is the summary:

Block 1920000 contained the execution of an irregular state change which transferred

12 million ETH from the «Dark DAO» and «Whitehat DAO» contracts into the WithdrawDAO recovery contract.

These irregular state changes don’t appear in Parity traces, so I had to query them with this SQL: https://github.com/blockchain-etl/ethereum-etl-airflow/blob/master/sqls/daofork_traces.sql. The result is then hardcoded in the Ethereum ETL tool and ingested to the BigQuery dataset.

trace_type column for these irregular traces is daofork . Another special type of traces that I had to add to the table is genesis, which are the initial Ether allocations. They are not associated with any transactions and thus not returned in transactions or traces APIs.


Johannes Pfeffer from alethio helped identify addresses for which the balances shown in the query results doesn’t match with the balances reported by the Ethereum nodes. Here is the list of the addresses:

The presumable root cause is this issue in Parity https://github.com/paritytech/parity-ethereum/issues/7765, which results in that the calls to precompiled contracts are missing from API results.

We will need to resync our Parity nodes, rerun the ETL, after which this issue will be resolved.


It’s possible to query running Ether balances for every address on every date. Try to compose a query that will return the number of addresses with non-zero balance, and plot it over time. Post your SQL in the comments. (Solution)

Далее будет дана базовая вводная информация для понимания общей картины и, без деталей информационной безопасности, рекомендации по безопасному владению аккаунтами/контрактами в Ethereum.

Гид по владению аккаунтами и контрактами в Ethereum

Ethereum, however, is more widely used, and its broad flexibility results in a rich, public dataset of transactional behavior. Because Ethereum addresses are unique identifiers whose ownership does not change, their activity can be tracked, aggregated, and analyzed.

Clustering Ethereum Addresses

Because Ethereum addresses are unique identifiers whose ownership does not change, their activity can be tracked, aggregated, and analyzed.

Motivation (читать далее...)
Clustering is an unsupervised machine learning technique, so I could not use labels to train my model. Instead, I used them to assign user archetypes to clusters, based on the highest label density for each cluster. Results can be found here.

But how do we know if it’s working?

By substituting results from re-clustering into the original analysis, we end up with 9 clusters. Exchanges

We can draw conclusions about user behavior based on the corresponding cluster centroids.
The database was constructed using SQL, and the model was coded in Python. Source code is available on GitHub.


io block explorer, I gathered crowdsourced labels for 125 addresses in my dataset.

Choosing the Appropriate Number of Clusters

This choice minimizes the number of samples with negative silhouette scores, which indicate that a sample may be assigned to the wrong cluster.

Если Вам нужна помощь в открытии и сохранении своих приватных ключей, то обратитесь к статье: Как установить холодный кошелек Ethereum

Понимание концепции закрытого ключа, открытого ключа и адреса в блокчейне Ethereum

Если Вам нужна помощь в открытии и сохранении своих приватных ключей, то обратитесь к статье Как установить холодный кошелек Ethereum.

Простой пример (читать далее...)
«Где pu — открытый ключ, предполагается, что это байтовый массив размером 64 (сформированный из конкатенации двух положительных целых чисел каждое

Открытый ключ (Public key)

Чтобы сгенерировать адрес Ethereum, возьмите хеш открытого ключа Keccak-256. Самые правые 20 байт — это и есть ваш адрес Ethereum. Закрытый ключ (Private Key)

Генерация ключей в Ethereum
Прежде чем мы углубимся в концепцию блокчейна Ethereum, важно понять, как генерируются открытый ключ, закрытый ключ и адрес.
Это всего лишь пример, чтобы понять концепцию. Пожалуйста, не используйте его для создания реального кошелька для хранения ETH.

Адрес Эфириума (Ethereum Address)

Где pu — открытый ключ, предполагается, что это байтовый массив размером 64 сформированный из конкатенации двух положительных целых чисел каждое.

Закрытый ключ (секретный большой номер) создается вашим кошельком случайным образом, затем шифруется (с вашим паролем) и сохраняется в файле UTC в папке хранилища ключей. В тоже время ваш публичный адрес рассчитывается на основе открытого ключа, полученного из закрытого.

If you do not wish to sell all your Fee Share balance in your current address, transfer the Fee Share you do not wish to sell to another address.

New Smart Contract Address

If you wish to transfer a Genesis Address Level 2 or 3, the receiver address must first have this option enabled.

(читать далее...)
— If your address is a Genesis Address, make sure your address is upgraded to Genesis Address Level 3. Otherwise the transfer will fail.

👉 Each Genesis Addresses received 32, 000 MNE. The Genesis Addresses are different from Normal Addresses because they self-mine MNE, for more information about the self-mining process read here.

👉 Fee Share Holders are free to Set their Fee Shares for sale and transfer them to other addresses.
👉 To buy multiple Genesis Addresses you need multiple Ethereum Addresses, one for each.

Otherwise the transfer will fail.

👉 If you received a Genesis Address with 32, 000 MNE from the airdrop, you have 5 options:

All Ethereum Addresses With Balance

§ *No referrals required in order to receive. 1% pays out daily to all members, 100% passive.
*When 310% is received, a new deposit must be made to continue earning / receiving from any of the 4 ways.

Related posts