Analyze Bitcoin data with Google BigQuery

Google BigQuery now has public Bitcoin datasets that allow users to analyze historical transaction data using standard SQL. Below are a few example SQL statements for analyzing the following metrics:

• Total number of blocks.
• Number of transactions in the last 24 hours.
• Average number of transactions per hour this year (2020).
• Bitcoins sent in the last 24 hours.
• Average transaction value this year (2020).
• Average transaction value this year (2020).

Visit here for more information on how to add public data sets to your BigQuery instance.

Total number of blocks

select count("hash") 
from bigquery-public-data.crypto_bitcoin.blocks;

Number of transactions in the last 24 hours

select count("hash")
from bigquery-public-data.crypto_bitcoin.transactions as trans
where cast(trans.block_timestamp as Date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

Average number of transactions per hour this year (2020)

select count("hash") / 8760 as averageNumTrans
from bigquery-public-data.crypto_bitcoin.transactions as trans
where trans.block_timestamp_month between '2020-01-01' and '2020-12-31';

Bitcoins sent in the last 24 hours

select SUM(output_value) * 0.00000001 as bitcoinsSent
from bigquery-public-data.crypto_bitcoin.transactions as transwhere 
cast(trans.block_timestamp as Date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY);

Average transaction value this year (2020)

select 
(sum(output_value) / count("hash") * 0.00000001) as averageTransactionFee
from bigquery-public-data.crypto_bitcoin.transactions as transwhere 
trans.block_timestamp_month between '2020-01-01' and '2020-12-31';

Average transaction fee this year (2020)

select 
(sum(fee) / count("hash") * 0.00000001) as averageTransactionValue
from bigquery-public-data.crypto_bitcoin.transactions as trans
where trans.block_timestamp_month between '2020-01-01' and '2020-12-31';