Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Google BigQuery Ethereum Addresses
#standardSQL
with double_entry_book as (
-- debits
select to_address as address, value as value
from `bigquery-public-data.crypto_ethereum.traces`
where to_address is not null
and status = 1
and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
union all
-- credits
select from_address as address, -value as value
from `bigquery-public-data.crypto_ethereum.traces`
where from_address is not null
and status = 1
and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
union all
-- transaction fees debits
select miner as address, sum(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value
from `bigquery-public-data.crypto_ethereum.transactions` as transactions
join `bigquery-public-data.crypto_ethereum.blocks` as blocks on blocks.number = transactions.block_number
group by blocks.miner
union all
-- transaction fees credits
select from_address as address, -(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value
from `bigquery-public-data.crypto_ethereum.transactions`
)
select address, sum(value) as balance
from double_entry_book
where value > 1 AND value < 5000
group by address
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment