Web3 SQL Weekly #1: How to calculate ETH balances for any address

Understand how ETH (native tokens) really work in transactions, and find out how to calculate balances for any EVM chain!

Welcome to Web3 SQL Weekly! I’ll be breaking down one (community submitted) query a week into byte-sized bits, making both the SQL and Blockchain concepts more digestible.

You can find all the weeks here, and submit/vote on future week queries here.

If you’re a complete beginner, you can reference this guide for any terms that are unfamiliar.

The video goes much more in the depth on explanations and examples, but you can also just read the written overview below:

What:

Today, I’ll cover calculating net balance of ETH for an address. The query will work for any EVM chain to get the balance of the native token.

Thank you Denze for submitting the query for this week!

Blockchain concepts:

ETH is the native token on the Ethereum chain. You may have heard of WETH or USDC; those are ERC20 tokens that are fundamentally different - those live in smart contracts.

ETH is instead directly stored in the state of the blockchain and is used for gas payments (among other things). For Fantom, the equivalent would be FTM; For Avalanche it would be AVAX. Rollups like Arbitrum and Optimism still use ETH as the native token.

The native token comes with its own native function. This function shows up in the data as a trace. To better understand this:

• Let’s look at a transfer tx, and we’ll see that there is always a `CALL` made to transfer ETH from one address to another. This is the same as what you’ll see on Etherscan in “value” - at least surface level.

  

• Let’s look at minting an NFT, and see that there are multiple calls made (4 in total) for the whole transaction. We can ignore the other calls in traces for now. However on Etherscan, you’ll only see that first transfer from the signing wallet. So we need to use a more advanced tool (in the links earlier).

  

This goes to show you that while an the transaction level you can see the value of ETH transferred from the transaction signers wallet, you have to go to a traces level to see ALL of the transfers.

So for our query, we’ll be using the ethereum.traces table. For Optimism and Ethereum, you could use transfers_optimism.eth or transfers_ethereum.eth instead and only filter on the tx_from and tx_to columns.

SQL Query Walkthrough:

Let’s use the data degens wallet address for this exercise.

First, we need to calculate all ETH inflows to an address. Remember, the raw value is in Wei (it’s 18 decimals). So we have to divide by pow(10,18) or 1e18. The `to` column represents the receiving address, so that’s why we GROUP BY and WHERE filter on that column.

You also have to filter on `success` to not accidently include transfers that didn’t actually go through.

We put the result into a CTE that we can reference later on. We can see that as of 1/20/2023, there was just one transfer of 0.05 into this wallet.

Next, we do same thing for ETH outflows from an address. Most of the times you don’t need to put quotes around columns, but `from` is a reserved keyword in Dune SQL. This address had no ETH transfers, so then we actually get no rows returned.

If that’s the case then how did the balance decrease? Well, that’s because every transaction you sign costs gas! So you must keep track of gas fees and subtract that from the total balance. We use the gas fee table and multiply gas_price_gwei by gas_used, where gwei is 10^9 (1e9).

Lastly, let’s JOIN all the data together to get the inflows, outflows, and balance. It doesn’t matter which JOIN we use here (LEFT, INNER, OUTER, CROSS, etc) because we’re just doing 1=1 on one row. If we were passing a list of addresses, then we would have to use LEFT and we would join ON “address” instead (you would need to add “address” to each CTE earlier, with a GROUP BY 1). We SELECT from transfers_in because the address must have had ETH come in to have ETH go out, so it’s safe to start there then join the other CTEs on.

Remember how eth_out was empty earlier? This means we get a null value in this final table. You can’t subtract/add null values otherwise the whole row becomes null, so we have to use a COALESCE to say “if null, then just use 0”. We use the same logic for gas_spent, because if you were looking at the balance of a contract address then gas_spent would be null since contracts can’t sign transactions!

Altogether, we can see that the balance of ETH is 0.00922375 and that it matches the value we see on Etherscan. Sometimes there will be small differences, as Dune is still ingesting the latest data from the chain.

Let’s put it in a counter so that it’s easy to include in a dashboard or share a screenshot later.

You can find the whole query on Dune, and fork it/play with it however you want.

The logic used here in terms of transfers in minus transfers out can be used for ERC20 tokens too. Check out this query for USDC balances.

Bonus question:

How can you determine the main sources of in and outflow of ETH, given an address? Check out the answer for the ETH2 Deposit contract. This one takes some mental flipping in terms of what fields to group by to get the right address based on filtering for transfers in “to” or transfers out “from” the deposit contract.

Hope you learned something! Don’t forget you can submit/vote on future week queries here.