A Practical Guide to Querying Bitcoin On-Chain Data

·

Bitcoin’s blockchain is far more than a digital ledger—it’s a treasure trove of transparent, publicly accessible data that reveals the pulse of the network. With the right tools and techniques, anyone can explore transaction trends, track supply dynamics, and uncover behavioral patterns of holders. This guide walks you through how to analyze Bitcoin on-chain data using powerful tools like Google BigQuery and DuckDB, with practical SQL examples for extracting meaningful insights.

Whether you're a data analyst, blockchain enthusiast, or developer, understanding how to query blockchain data empowers you to make informed decisions based on real network activity.

Where to Query Bitcoin Blockchain Data

The most efficient way to analyze large-scale Bitcoin data is by leveraging Google BigQuery’s public dataset for Bitcoin. It provides structured access to the entire transaction history, enabling complex queries without running a full node.

SELECT
 COUNT(*) as transactions
FROM `bigquery-public-data.crypto_bitcoin.transactions`

However, there’s an important caveat: BigQuery offers 1 TB of free data processing per month, but the Bitcoin transactions table exceeds 2 TB (as of early 2025). Running inefficient queries could lead to unexpected costs.

👉 Discover how to analyze blockchain data without high query fees

To avoid charges, consider this smart workaround: export the entire dataset to Google Cloud Storage (GCS). Exporting from BigQuery to GCS is completely free. Save the data in compressed Parquet format to reduce storage size. Once stored, you can download these files locally or process them using lightweight tools like DuckDB, which efficiently reads Parquet files directly from disk.

While the storage location may vary, the core querying logic remains consistent—making your analysis portable across platforms.

Analyzing Daily Transaction Volume

One of the most fundamental metrics in blockchain analysis is daily transaction count. Each row in the transactions table represents a single Bitcoin transaction, so calculating volume is straightforward.

Here’s a SQL query to extract daily transaction counts:

SELECT
 DATE(block_timestamp, 'America/Los_Angeles') as date,
 COUNT(DISTINCT tx.hash) as num_tx
FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx
GROUP BY 1
ORDER BY 1 DESC

This query groups transactions by date (adjusted to Pacific Time) and returns the number of unique transactions per day. Over time, this data reveals adoption trends, network congestion periods, and macro-level user activity.

Pro Tip: Filter by specific date ranges or exclude low-value “dust” transactions to refine accuracy and reduce processing costs.

Calculating Bitcoin’s Total Supply

Bitcoin has a capped supply of 21 million BTC—but how do we verify the current circulating supply using on-chain data?

The answer lies in UTXOs (Unspent Transaction Outputs). Every Bitcoin transaction consumes inputs (previous UTXOs) and creates new outputs. Only outputs that haven’t been spent—UTXOs—contribute to the total supply.

Using BigQuery, we can calculate the sum of all unspent outputs:

DECLARE DateEnd DATE DEFAULT CURRENT_DATE() + 1;
WITH outs AS (
 SELECT
 tx.hash as tx_id,
 o.index,
 o.value
 FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx, UNNEST(tx.outputs) as o
 WHERE block_timestamp_month <= DateEnd
),
ins AS (
 SELECT
 i.spent_transaction_hash as tx_id,
 i.spent_output_index as index
 FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx, UNNEST(tx.inputs) as i
 WHERE block_timestamp_month <= DateEnd
)
SELECT
 SUM(outs.value)/1e8 as btc_total_supply
FROM outs
LEFT JOIN ins
ON ins.tx_id = outs.tx_id AND ins.index = outs.index
WHERE ins.tx_id IS NULL

This query:

The result gives you the actual circulating supply at any point in time—validating one of Bitcoin’s core promises: scarcity.

Measuring Coin Hotness: Tracking UTXO Age

"Coin hotness" refers to how recently a UTXO was moved. It helps distinguish between active traders and long-term holders.

Analyzing coin hotness over time reveals market sentiment shifts—such as when long-term holders cash out during bull runs.

Here’s a comprehensive query that categorizes UTXOs by age:

DECLARE DateEnd DATE DEFAULT CURRENT_DATE() + 1;
DECLARE Days DEFAULT [DateEnd];
WITH outs AS (
 SELECT d as date, tx.block_timestamp, tx.hash as tx_id, o.index, o.value
 FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx,
 UNNEST(tx.outputs) as o,
 UNNEST(Days) as d
 WHERE block_timestamp_month <= DateEnd AND DATE(tx.block_timestamp) <= d
),
ins AS (
 SELECT d as date, tx.block_timestamp, i.spent_transaction_hash as tx_id, i.spent_output_index as index
 FROM `bigquery-public-data.crypto_bitcoin.transactions` as tx,
 UNNEST(tx.inputs) as i,
 UNNEST(Days) as d
 WHERE block_timestamp_month <= DateEnd AND DATE(tx.block_timestamp) <= d
)
SELECT
 *,
 SUM(btc) OVER (PARTITION BY date) as btc_total_supply,
 ROUND(100 * btc / SUM(btc) OVER (PARTITION BY date), 2) as btc_supply_share
FROM (
 SELECT
 outs.date,
 CASE
 WHEN DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 7 THEN '1. <7 DAY'
 WHEN DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 35 THEN '2. <35 DAY'
 WHEN DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 90 THEN '3. <90 DAY'
 WHEN DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 180 THEN '4. <180 DAY'
 WHEN DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 365 THEN '5. <1 YEAR'
 WHEN DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) < 365*5 THEN '6. <5 YEARS'
 WHEN DATE_DIFF(outs.date, DATE(outs.block_timestamp), DAY) >= 365*5 THEN '7. >5 YEARS'
 END as hotness,
 SUM(outs.value/1e8) as btc
 FROM outs
 LEFT JOIN ins ON ins.date = outs.date AND ins.tx_id = outs.tx_id AND ins.index = outs.index
 WHERE ins.tx_id IS NULL
 GROUP BY 1, 2
)
ORDER BY 1 DESC, 2

This query shows what percentage of total supply falls into each age band—helping detect macro trends like accumulation phases or profit-taking events.

👉 See how real-time on-chain analytics can inform your investment strategy

Frequently Asked Questions (FAQ)

What is on-chain data?

On-chain data refers to all transactions recorded on the blockchain. It includes sender/receiver addresses, timestamps, amounts, fees, and script types—all publicly verifiable.

Can I analyze Bitcoin data without coding?

Yes—tools like Glassnode or CoinMetrics offer visual dashboards. However, direct querying via SQL gives deeper control and customization for advanced users.

Is Google BigQuery safe for blockchain analysis?

Yes. BigQuery is secure and scalable. Just monitor your queries to stay within the free tier and avoid excessive data scanning.

What does UTXO mean?

UTXO stands for Unspent Transaction Output. It represents bitcoins not yet used as inputs in new transactions—essentially the building blocks of ownership.

How accurate is coin hotness analysis?

Highly accurate when aggregated. Individual wallet behaviors may vary due to mixing or cold storage practices, but overall trends remain reliable indicators of market psychology.

Can I run these queries offline?

Yes. After exporting data to Parquet via GCS, use DuckDB or Apache Spark to run SQL-like queries locally—eliminating cloud costs entirely.

Final Thoughts

Bitcoin’s blockchain isn’t just secure and decentralized—it’s analyzable. By applying SQL-based analysis to on-chain data, you gain visibility into network health, user behavior, and economic activity.

From counting daily transactions to measuring coin hotness and verifying total supply, these techniques open doors to deeper understanding. And once you start exploring, you’ll find that the insights are limited only by your curiosity—not the technology.

Whether you're validating Bitcoin’s scarcity or detecting early signs of market shifts, mastering on-chain analysis puts real power in your hands.

👉 Start exploring live blockchain data with powerful trading tools