List Total Active Ethereum wallets

Let's make a chart for the user accounts that have interacted with the Ethereum network at least once.

Where are the user accounts stored?

EVM maintains the user accounts and their balances in the account state. However, Dune Analytics does not yet support the state.

So, how do we get it?

We know that any Ethereum global state changes are triggered by a transaction from a user account (generally referred to as "Externally Owned Accounts").

Externally Owned Accounts(EOA) are defined by:

• a private key known only to the user.
• a public key that is available to anyone who wants.

Whenever a transaction is created, the wallet address (derived from the public key) is included in it.

Wallet Address in Transaction

We can query the from column in the transactions table to retrieve all of the user accounts that have interacted with the Ethereum network. Since the same account may have interacted multiple times, let's use DISTINCT to find the unique ones.

/* Query to retrieve total number of active wallets */
SELECT
  COUNT(DISTINCT `from`)/1e6 AS active_user_accounts
FROM
  ethereum.transactions;

There could have been other external accounts that received ETH or other assets but never initiated a transaction. We will not be able to find those in the from column. For the scope of this task, we are only considering accounts that have initiated a transaction at least once.

👨🏽‍💻 Can you create a counter chart for the above query, as you did in the previous section?

👉🏽 Please remember to add the chart to the current dashboard.

Feeling stuck? Check out this Active Wallets Chart