Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    medvedev1088
    @medvedev1088
    medvedev1088
    @medvedev1088
    NUMERIC unlike FLOAT64 in BigQuery doesn't have precision loss for aritmetic operations.
    Rahul
    @rraina
    @medvedev1088 -- I got why the missing dates were dropping addresses. DUH! Thanks a bunch :)
    Rahul
    @rraina
    @medvedev1088 -- query for Pareto. Limited to top 1M because it covers >99% of total, and doesn't cause out of memory errors :) Thoughts?
    #standardSQL
    with double_entry_book as (
        -- debits
        select to_address as address, value as value, block_timestamp
        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, block_timestamp
        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)
        union all
        -- transaction fees debits
        select miner as address, sum(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value, block_timestamp
        from `bigquery-public-data.ethereum_blockchain.transactions` as transactions
        join `bigquery-public-data.ethereum_blockchain.blocks` as blocks on blocks.number = transactions.block_number
        group by blocks.miner, block_timestamp
        union all
        -- transaction fees credits
        select from_address as address, -(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) as value, block_timestamp
        from `bigquery-public-data.ethereum_blockchain.transactions`
    ),
    balances_by_address as (
        select address, sum(value) as balance
        from double_entry_book
        group by address
    ),
    balances_total AS (
      SELECT SUM(value) AS total_balance
      FROM double_entry_book
    ),
    balances_by_address_top_million AS (
      SELECT address, balance
      FROM balances_by_address
      ORDER BY balance DESC, address
      LIMIT 1000000 OFFSET 0
    ),
    cumm_balances_by_address_top_million AS (
      SELECT address, balance, SUM(balance) OVER(ORDER BY balance DESC, address ASC) AS cumm_balance
      FROM balances_by_address_top_million
    )
    SELECT address, balance, cumm_balance, (cumm_balance / total_balance) AS perc_total 
    FROM cumm_balances_by_address_top_million
    INNER JOIN balances_total ON TRUE
    ORDER BY perc_total ASC
    medvedev1088
    @medvedev1088
    @rraina
    Nice! I’m also figuring out how to plot Gini coefficient.
    Rahul
    @rraina
    @medvedev1088 -- That would be cool. I might try using the relative mean absolute difference formula to do it in SQL...
    Will post here when I try it this weekend
    Rahul
    @rraina
    @medvedev1088 -- I don't know if I am doing something wrong, but the MKR contract doesn't show up in the contracts table..
    SELECT c.address, c.is_erc20, c.is_erc721 
    FROM `bigquery-public-data.ethereum_blockchain.contracts` c
    WHERE c.address  = '0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2'
    medvedev1088
    @medvedev1088
    @rraina this may be because the MKR contract was created in an internal transaction. Right now the contracts table is populated from receipts, instead it should be populated from create traces. We have a task for that.
    Rahul
    @rraina
    @medvedev1088 -- I just checked. You are correct. It was created from a parent contract.
    Rahul
    @rraina
    Can you point me to the Issue on Github -- couldn't find it :/
    medvedev1088
    @medvedev1088
    @rraina just created this issue blockchain-etl/ethereum-etl-airflow#8 :)
    @evgeniuz the issue has been funded blockchain-etl/ethereum-etl-airflow#2. I invited you to the team. I will assign you to the task so other people don't apply.
    bb324930
    @bb324930
    I haven't been able to track down a specific block, but it appears there is a bug that fails to extract some ERC20 transfers.
    -- amount flowing into address
    select
    sum((cast(t.value as numeric))) as total
    from `bigquery-public-data.ethereum_blockchain.token_transfers` t
    where t.token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
    and t.to_address = '0x4969358e80cdc3d74477d7447bffa3b2e2acbe92'
    
    UNION ALL
    
    -- amount flowing out of address (CANNOT be greater than the above)
    select
    -sum((cast(t.value as numeric))) as total
    from `bigquery-public-data.ethereum_blockchain.token_transfers` t
    where t.token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
    and t.from_address = '0x4969358e80cdc3d74477d7447bffa3b2e2acbe92'
    The debits are larger than the credits, which is impossible.
    The token address is a well known ERC20 contract, WETH
    I have confirmed on my local version of ethereum-etl that this is also the case, not just Google's ETL
    bb324930
    @bb324930
    The other explanation is that other contracts have allowed the movement of this token. For example, old contracts replaced by newer ones
    medvedev1088
    @medvedev1088
    @bb324930 you should also include Deposit and Withdrawal events in your double entry book. You can query them from the logs table. token_transfers only includes the Transfer event.
    bb324930
    @bb324930

    @medvedev1088 do you mean the transferFrom call? this also calls the Transfer() event:

    function transferFrom(address from, address to, uint tokens) public returns (bool success) { balances[from] = balances[from].sub(tokens); allowed[from][msg.sender] = allowed[from][msg.sender].sub(tokens); balances[to] = balances[to].add(tokens); Transfer(from, to, tokens); return true; }

    so Transfer logs should encompass this as well
    medvedev1088
    @medvedev1088
    @bb324930 I meant deposit() and withdraw() methods. They change the balances also:
    function deposit() public payable {
        balanceOf[msg.sender] += msg.value;
        Deposit(msg.sender, msg.value);
      }
    bb324930
    @bb324930
    Interesting - where are you finding this? I don't see it in the reference:
    https://github.com/ethereum/EIPs/blob/master/EIPS/eip-20.md
    medvedev1088
    @medvedev1088
    Rahul
    @rraina
    Happy New Years folks!
    Rahul
    @rraina
    Congrats!!
    GuthL
    @GuthL
    @medvedev1088 Congrats for the good work!
    I’m looking into withdrawing the public key from the transaction data for all active addresses. Is there a way to do it with ETL?
    Rahul
    @rraina
    Yes.
    Just write a SQL query to get unique addresses and union the from and to addresses
    chandanthundercore
    @chandanthundercore
    How do we get new EVM compliant chain added to this ETL? ThunderCore just released their mainnet. Is there a way to have ETL analyze their blockchain?
    GuthL
    @GuthL

    Just write a SQL query to get unique addresses and union the from and to addresses

    @rraina I’m not sure we are talking about the same things here. The address is not the public key. To extract a public key associated with a specific address, I need to extract it from an emitted transaction. I don’t know if ETL stores them.

    Rahul
    @rraina
    ETL stores every transaction including traces. Have you had a chance to look at the dataset on BigQuery?
    GuthL
    @GuthL
    Thanks a lot! I did but could not really make completely sense of it. From what you told me, it looks like what I’m looking for is under input in the transactions table.
    Rahul
    @rraina
    You want to look at the from_address and to_address I think...
    medvedev1088
    @medvedev1088
    @GuthL the JSON RPC api also returns v, r, s values of the ECDSA signature of a transaction https://github.com/ethereum/wiki/wiki/JSON-RPC#eth_gettransactionbyhash, from which it should be possible to recover the public key. Ethereum ETL doesn't export these values though. Feel free to create a PR. btw why are you interested in public keys and not addresses as Rahul suggested?
    medvedev1088
    @medvedev1088

    How do we get new EVM compliant chain added to this ETL? ThunderCore just released their mainnet. Is there a way to have ETL analyze their blockchain?

    @chandanthundercore if you want to to add ThunderCore support to the ethereum-etl tool you can create a PR. If you want to add it to BigQuery public datasets we'll need to discuss it with a Google representative.

    Eric Price
    @EricPrice2_twitter
    has anyone experienced getting this error when running "export_all" using geth via RPC?
    INFO [04-03|04:12:19.947] Served eth_getFilterLogs conn=192.168.1.10:54074 reqid=1 t=251.823µs err="context canceled"
    get this python error when it reaches token_transfers
    ValueError: {'code': -32000, 'message': 'context canceled'}
    medvedev1088
    @medvedev1088
    @EricPrice2_twitter is it reproduced consistently or from time to time?
    Eric Price
    @EricPrice2_twitter
    I can reproduce this every time
    medvedev1088
    @medvedev1088
    @EricPrice2_twitter I created an issue here blockchain-etl/ethereum-etl#165
    Veikko Eeva
    @veikkoeeva
    This appears the be a channel to ask if anyone knows if it possible to save transaction traces while any of the clients are syncing a full node? I mean I'd like to avoid the issue of saving an archive node but would like to record the transactions to a separate persistence system while syncing and then maintain just pruned full node and record transactions from the tip as they appear. I wonder if this is wishful thinking or possible without taking one the clients and modifying the actual code?
    Maybe one option would be to download the BigQuery dataset as the baseline.