2

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).

3

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.

| improve this answer | |
1

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.crypto_ethereum.traces`);

with old_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 `bigquery-public-data.crypto_ethereum.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.crypto_ethereum.traces`
    where from_address is not null
    and status = 1
    and `bigquery-public-data.crypto_ethereum.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.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)
)
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.crypto_ethereum.contracts`.address from `bigquery-public-data.crypto_ethereum.contracts` where `bigquery-public-data.crypto_ethereum.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.crypto_ethereum.contracts`.address from `bigquery-public-data.crypto_ethereum.contracts` where `bigquery-public-data.crypto_ethereum.contracts`.address = double_entry_book.address) and wee.eulav<500000000000000000000.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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.