Proof of Reserves for Bitcoin Holdings — How to Compute in BigQuery

Published in

Coinmonks

3 min readDec 12, 2022

The collapse of FTX sent shockwaves through the crypto world. The debacle could have been avoided if crypto had stuck to its core tenets — don’t trust, verify and always self-custody your assets.

As contagion from FTX is spreading, centralized exchanges are seeking to rebuild investor confidence. Major exchanges have shared details of their hot and cold wallet addresses as part of their commitment to transparency and fostering trust in the ecosystem.

In this article, we will use Google BigQuery to query Bitcoin balances using the wallet addresses disclosed by the exchanges. You may refer to this list for the BTC wallet addresses.

Proof of reserves on cryptocurrency exchanges

For your ease of reference, the BTC wallet addresses belonging to Binance are shown here:

If you prefer not to run the query, you may view BTC balances on the Bitcoin Dashboard. Make sure to choose ‘Select Category’ as Exchange and choose ‘Exchange’ as Binance Balance.

The full query to compute the BTC balances on Binance exchange is shown below. Details of the code was discussed in Guide to Query Bitcoin Balances Using BigQuery.

WHERE was used in ‘double_entry_book_grouped’ to partition the data as early as possible to reduce computational resources and improve performance.

WHERE contains_substr(`address`, `string`) — use to search for BTC wallet address

WITH double_entry_book AS (
    -- debits
    SELECT array_to_string(inputs.addresses, ",") AS address, -inputs.value AS value, 
    DATE(block_timestamp) AS date
    FROM `bigquery-public-data.crypto_bitcoin.inputs` AS inputs
    UNION ALL
    -- credits
    SELECT array_to_string(outputs.addresses, ",") AS address, outputs.value AS value, 
    DATE(block_timestamp) AS date
    FROM `bigquery-public-data.crypto_bitcoin.outputs` AS outputs
), 

double_entry_book_grouped AS (
    SELECT date, address, SUM(value / POWER(10,8)) AS value
    FROM double_entry_book

    -- Binance
    WHERE contains_substr(`address`, '34xp4vRoCGJym3xR7yCVPFHoCNxv4Twseo') OR
    -- Binance 2
    contains_substr(`address`, '3LYJfcfHPXYJreMsASk2jkn69LWEYKzexb') OR
    -- Binance 3
    contains_substr(`address`, '3M219KR5vEneNb47ewrPfWyb5jQ2DjxRP6') OR
    -- Binance 4
    contains_substr(`address`, 'bc1qm34lsc65zpw79lxes69zkqmk6ee3ewf0j77s3h')
    
    GROUP BY date, address
),

daily_balances_gappy AS (
    SELECT date, address,
    SUM(value) OVER (PARTITION BY address ORDER BY date) AS balance,
    LEAD(date, 1, CURRENT_DATE()) OVER (PARTITION BY address ORDER BY date) AS next_date
    FROM double_entry_book_grouped
),

all_dates AS (
    SELECT date FROM UNNEST(GENERATE_DATE_ARRAY('2009-01-01', CURRENT_DATE())) AS date
),

daily_balances_gapless AS (
    SELECT address, all_dates.date, balance
    FROM daily_balances_gappy
    JOIN all_dates ON daily_balances_gappy.date <= all_dates.date AND all_dates.date < daily_balances_gappy.next_date
),

all_daily_balances AS (
    SELECT DISTINCT date, SUM(balance) OVER(PARTITION BY date) AS balance
    FROM daily_balances_gapless
)

SELECT * FROM all_daily_balances
ORDER BY date

Conclusion

You may try this code for other exchanges by replacing the string in WHERE contains_substr(`address`, `string`) with their respective wallet addresses. The BTC wallet addresses of major exchanges are provided here.

Stay safe. Remember “Not your keys, not your coins”.

Proof of Reserves for Bitcoin Holdings — How to Compute in BigQuery

Conclusion

Written by Vivien Chua