Bitcoin

Schema: Bitcoin

This schema contains several on-chain datasets for tracking Bitcoin ownership and flows over time.

Sample Query

// Pull number of addresses with more than 10 BTC over time
select 
    date, 
    addresses
from 
    bitcoin.addresses_with_balance_gte_ten
order by date asc

// Amount of BTC in circulation by different age bands
select
    date,
    UTXO_VALUE_UNDER_1D / TOTAL_UTXO_VALUE * 100 as "1D",
    UTXO_VALUE_1D_1W / TOTAL_UTXO_VALUE * 100 as "1D-1W",
    UTXO_VALUE_1W_1M / TOTAL_UTXO_VALUE * 100 as "1W-1M",
    UTXO_VALUE_1M_3M / TOTAL_UTXO_VALUE * 100 as "1M-3M",
    UTXO_VALUE_3M_6M / TOTAL_UTXO_VALUE * 100 as "3M-6M",
    UTXO_VALUE_6M_12M / TOTAL_UTXO_VALUE * 100 as "6M-12M",
    UTXO_VALUE_1Y_2Y / TOTAL_UTXO_VALUE * 100 as "1Y-2Y",
    UTXO_VALUE_2Y_3Y / TOTAL_UTXO_VALUE * 100 as "2Y-3Y",
    UTXO_VALUE_3Y_5Y / TOTAL_UTXO_VALUE * 100 as "3Y-5Y",
    UTXO_VALUE_5Y_7Y / TOTAL_UTXO_VALUE * 100 as "5Y-7Y",
    UTXO_VALUE_7Y_10Y / TOTAL_UTXO_VALUE * 100 as "7Y-10Y",
    UTXO_VALUE_GREATER_10Y / TOTAL_UTXO_VALUE * 100 as ">10Y"
from
    bitcoin.hodl_wave
order by
    date ASC    

// BTC Miner fees vs BTC price over time
SELECT
    miner_fees.date,
    miner_fees.total_reward,
    price
from
    bitcoin.miner_fees
    JOIN common.daily_market_data on bitcoin.miner_fees.date = common.daily_market_data.date
where
    coingecko_id = 'bitcoin'

Table name	Descriptions
ADDRESSES_WITH_BALANCE_GTE_POINT_ZERO_ONE	Number of addresses with >= 0.01 BTC over time
ADDRESSES_WITH_BALANCE_GTE_ONE	Number of addresses with >= 1 BTC over time
ADDRESSES_WITH_BALANCE_GTE_TEN	Number of addresses with >= 10 BTC over time
ADDRESSES_WITH_BALANCE_GTE_ONE_HUNDRED	Number of addresses with >= 100 BTC over time
HODL_WAVE	Amount of BTC in circulation grouped into different age bands. More information can be found here.
MINER_FEES	Block reward, fees (paid by users), and total rewards (sum of block reward and fees)