Since my previous article Ethereum Blockchain on Google BigQuery I added receipts , logs , contracts tables to the public dataset https://bigquery.cloud.google.com/dataset/bigquery-public-data:crypto_ethereum (you can use it for free), and also moved it to the US region so I could connect it to Google Data Studio.

First of all, there are almost 2 million contracts in the contracts table. Only around 30 thousand of them are verified, according to Etherscan, which is just about 2%.

The below chart shows contract creations and average bytecode length:

#standardSQL

SELECT

transactions.block_timestamp,

contracts.address,

LENGTH(contracts.bytecode) AS bytecode_length

FROM

`bigquery-public-data.crypto_ethereum.contracts` as contracts

JOIN `bigquery-public-data.crypto_ethereum.transactions` as transactions

ON contracts.address = transactions.receipt_contract_address

WHERE transactions.receipt_contract_address is not null

You can notice an interesting spike on October 4, 2016 with almost 23k new contracts, and a drop in new contracts with simultaneous increase in average bytecode length on March 17, 2018.

The above chart shows successful (blue) and failed (red) transactions based on the receipt_status column. This column was added in Bysantium release that’s why the data is missing before October 14, 2017.

#standardSQL

SELECT

address,

LENGTH(bytecode) AS bytecode_length

FROM `bigquery-public-data.crypto_ethereum.contracts`

ORDER BY 2 DESC

LIMIT 10

This table lists contracts with biggest bytecode length. The biggest one was added quite recently https://etherscan.io/address/0x9408e006843ade4a6966e04b58af459bec21e899. The second one is interesting as it’s very active and has almost 100 Ether https://etherscan.io/address/0x10c621008b210c3a5d0385e458b48af05bf4ec88

#standardSQL

SELECT

MAX(address) as contract_address_sample,

count(bytecode) AS contract_count

FROM `bigquery-public-data.crypto_ethereum.contracts`

GROUP BY bytecode

ORDER BY 2 DESC

LIMIT 10

This one shows that, for example this bytecode https://etherscan.io/address/0xffffe35ae8b58e8b6acea8585180519307fec280#code has been uploaded to the blockchain 380287 times. You can find similar stats on Etherscan page:

Unfortunately, it’s not verified, but I suspect it’s a kind of multisig wallet.

You can check out the tool that I used for exporting the data here https://github.com/medvedev1088/ethereum-etl

Also read: