I want the full list of ethereum smart‑contracts whose balances increased by 1000 Ethers above the past week

by user2284570   Last Updated October 19, 2019 16:28 PM - source

How to make this kind of query using my 200kb/s second Internet connection speed ?

With such speed, there’s no possibility to run an archival node (except maybe with a service allowing to download only recent blocks without the previous ones).



Answers 2


You need to use Ethereum Public Dataset and Google BigQuery. This is Ethereum blockchain data picked be Google on daily basis and saved into cloud data warehouse. The data is available for everybody to query.

Here is an article that one could use as a tutorial for writing BigQuery requests to Ethereum dataset: How to Query Balances for all Ethereum Addresses in BigQuery.

Mikhail Vladimirov
Mikhail Vladimirov
October 19, 2019 15:33 PM

Using the Ethereum Public Dataset and Google BigQuery, I was able to find my answer :

#standardSQL
-- MIT License
declare stamp TIMESTAMP default (SELECT MAX(block_timestamp) from `bigquery-public-data.ethereum_blockchain.traces`);

with old_double_entry_book as (
    -- debits
    select to_address as address, value as value
    from `bigquery-public-data.ethereum_blockchain.traces`
    where to_address is not null
    and status = 1
    and `bigquery-public-data.ethereum_blockchain.traces`.block_timestamp<TIMESTAMP_SUB(stamp, INTERVAL 168 HOUR)
    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.ethereum_blockchain.traces`
    where from_address is not null
    and status = 1
    and `bigquery-public-data.ethereum_blockchain.traces`.block_timestamp<TIMESTAMP_SUB(stamp, INTERVAL 168 HOUR)
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
), double_entry_book as (
    -- debits
    select to_address as address, value as value
    from `bigquery-public-data.ethereum_blockchain.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.ethereum_blockchain.traces`
    where from_address is not null
    and status = 1
    and (call_type not in ('delegatecall', 'callcode', 'staticcall') or call_type is null)
)
select concat('https://etherscan.io/address/',address) as adress, sum(value) as balance
from double_entry_book
left join (SELECT address as shah, sum(value) as eulav from old_double_entry_book     where exists (SELECT `bigquery-public-data.ethereum_blockchain.contracts`.address from `bigquery-public-data.ethereum_blockchain.contracts` where `bigquery-public-data.ethereum_blockchain.contracts`.address = old_double_entry_book.address) group by shah order by eulav desc) wee on wee.shah= double_entry_book.address
where exists (SELECT `bigquery-public-data.ethereum_blockchain.contracts`.address from `bigquery-public-data.ethereum_blockchain.contracts` where `bigquery-public-data.ethereum_blockchain.contracts`.address = double_entry_book.address) and wee.eulav<1000000000000000000000.0
group by adress
order by balance desc
limit 100

Of course there’s might be better but I don’t know how to use tables defined using with using previous table defined in the same with statement so I’m using twice the same subquery for old_double_entry_book.

user2284570
user2284570
October 24, 2019 15:10 PM

Related Questions



What is the shared nibble of this extension node?

Updated November 28, 2017 20:28 PM

Why is my Ethereum Wallet balance wrong?

Updated July 21, 2017 00:28 AM