Bitcoin: How to Query for Sending and Receiving Addresses in BigQuery
Continuing on from the previous article, we will explore how to query for sending and receiving addresses count on the Bitcoin blockchain with BigQuery. Through this article, you will have a better understanding of how to use BigQuery to analyze Bitcoin transaction data, and gain insights into sending and receiving address activity that can be used for various purposes, including investment analysis and fraud detection.
Sending addresses count refer to the number of unique addresses that were active as a sender of funds. Only addresses that were active as a sender in successful non-zero transfers are counted.
Receiving addresses count refer to the number of unique addresses that were active as a receiver of funds. Only addresses that were active as a receiver in successful non-zero transfers are counted.
Step 1
Go to the Editor field on BigQuery.
Step 2
In the Editor field, copy the query below for sending addresses.
The query calculates the number of unique sending addresses used in bitcoin transactions each day.
To get started, we create a common table expression (CTE) sending_addresses which selects the date and input addresses for each Bitcoin transaction from bigquery-public-data.crypto_bitcoin.inputs .
Next we create a CTE sending_addresses_grouped_by_date which groups the results from sending_addresses by date and counts the number of unique sending addresses for each date.
WITH sending_addresses AS (
-- input addresses
SELECT DATE(block_timestamp) AS date,
array_to_string(inputs.addresses, ",") as address FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
WHERE DATE(block_timestamp) > "2009-01-01" AND DATE(block_timestamp) <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
),
sending_addresses_grouped_by_date as (
SELECT date, COUNT(DISTINCT address) as sending_addresses_count
FROM sending_addresses
GROUP BY date
ORDER BY date
)
SELECT date, sending_addresses_count FROM sending_addresses_grouped_by_dateStep 3
In the Editor field, copy the query below for receiving addresses.
The query calculates the number of unique receiving addresses used in Bitcoin transactions each day.
In this query, the blockchain data is obtained from bigquery-public-data.crypto_bitcoin.outputs instead. This table contains information about the outputs of a Bitcoin transaction.
WITH receiving_addresses AS (
-- credits
SELECT DATE(block_timestamp) AS date,
array_to_string(outputs.addresses, ",") as address FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
WHERE date > "2015-01-01" AND date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
),
receiving_addresses_grouped_by_date as (
SELECT date, COUNT(DISTINCT address) as receiving_addresses_count
FROM receiving_addresses
GROUP BY date
ORDER BY date
)
SELECT date, receiving_addresses_count FROM receiving_addresses_grouped_by_dateYou may refer to Query Bitcoin Blockchain for Active Addresses in BigQuery for an explanation of the functions used in this source code.
Final Thoughts
The queries shown here provides information on the number of unique sending and receiving addresses used in Bitcoin transactions each day. This analysis allows us to gain insights into the flow of funds on the Bitcoin network. We are also able to track the growth and adoption of Bitcoin, as well as analyze transactional activity on the network. Overall, this is an invaluable resource for anyone interested in understanding the Bitcoin network.
Also read:
Thank you for reading!
If you liked the article and would like to see more, consider following me. I post regularly on topics related to on-chain analysis, machine learning and BigQuery. I try to keep my articles simple but precise, providing code, examples and simulations whenever possible.
