Query Bitcoin balance by timestamp in BigQuery

bigquery-bitcoin-balance.sql

WITH double_entry_book AS (
    -- debits
    SELECT
     array_to_string(inputs.addresses, ",") as address
    , inputs.type
    , -inputs.value as value
    FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
 
    UNION ALL
 
    -- credits
    SELECT
     array_to_string(outputs.addresses, ",") as address
    , outputs.type
    , outputs.value as value
    FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
)
SELECT 
    address
,   type    
,   sum(value) as balance
FROM double_entry_book
GROUP BY 1,2
ORDER BY balance DESC
LIMIT 1000