The Bitcoin blockchain isn’t just a buzzword; it’s a publicly accessible ledger full of valuable data, just waiting to be explored. Because it’s open to all, we have the unique ability to directly tap into and query the very heart of the Bitcoin network.

Where to query Bitcoin blockchain data?

There are many different ways to do it, however, my preferred way is using a public dataset in Google BigQuery.

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

There’s a little catch though, BigQuery is only free up to 1TB of data read, and the current size of the transactions table is 2TB (as of Feb. 25th, 2025). Consider this fair warning as some caution is needed to avoid incurring costs one might not expect.

Now, BigQuery’s pricing can be a little scary when dealing with such a large dataset. To avoid any unexpected bills, here’s a trick I’ve used: I export the entire transactions table from BigQuery to Google Cloud Storage (GCS). The beauty of this is that exporting out of BigQuery is completely free! Plus, I save it as compressed Parquet files to minimize storage.

From GCS, you can then download those parquet files to your local machine or an external drive. Boom! You’ve got the entire Bitcoin transaction history right at your fingertips, ready to be analyzed without racking up BigQuery costs.

Now you have a ton of parquet files, but how do we query it? Use duckdb, personally, I love working with it and it can read parquet files directly from the hard drive.

Wherever you end up querying it, the concepts in the queries will transfer across different sources, so worrying too much about where to do it likely isn’t necessary.

Number of Daily Transactions

A single row in the table is one transaction, so getting transactions per day is very easy.

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

Calculating Bitcoin’s Total Supply

Let’s build on our knowledge of what a transaction consists of in Bitcoin. Each transaction has inputs and outputs. Inputs are what “funds” the transaction, and outputs are where the funds are going.

To calculate the total supply of Bitcoin, that is all Bitcoin currently in circulation, or saying it a 3rd way, all mined Bitcoin, we need to sum up all UTXOs which is blockchain lingo for unspent transaction outputs.

Put simply, UTXOs are outputs that are not input to any other transaction.

Inputs and outputs in the BigQuery transactions table are nested fields attached to the transaction row itself. Quite convenient!

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 -- all unspend outputs (outputs without a following input) form total supply

Calculate Coin Hotness

Coin hotness measures the age of UTXOs. If a UTXO created from a transaction yesterday becomes spent the next day it would be considered very “hot” as the coins in the UTXO moved in a single day. On the other hand, someone can hold a UTXO in cold storage as a savings account and has never moved their coins, that UTXO that may have not moved 5+ years would be considered cold.

The calculation shows what types of UTXO are being spent. It’s best to view it over time to capture trends. Most typically you might see old UTXO being spent when long-time holders want to take the profits for example.

DECLARE DateEnd DATE DEFAULT CURRENT_DATE() + 1; # '2013-01-01';
DECLARE Days DEFAULT [DateEnd]; # GENERATE_DATE_ARRAY("2009-01-01", 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

Wrap up

And there you have it! We’ve walked through one genuinely basic query and a couple of others that may nudge beyond the “super” basic level, all aimed at letting you peek under the hood of the Bitcoin blockchain. I hope that you’re now seeing how your existing SQL skills can be leveraged to dissect, understand, and uncover cool trends within it.

This is just the tip of the iceberg. The potential insights hidden within the blockchain are practically limitless, and you’ll be surprised at how quickly the structure of the data becomes second nature.