Balance Query for Google BQ public bitcoin dataset

bitcoin_balances.sql

WITH double_entry_book AS (
    -- debits
    SELECT
      array_to_string(inputs.addresses, ",") as address
    , inputs.type
    , -inputs.value as value
    FROM `crypto-etl-bitcoin-prod.bitcoin_blockchain.inputs` as inputs

    UNION ALL

    -- credits
    SELECT
      array_to_string(outputs.addresses, ",") as address
    , outputs.type
    , outputs.value as value
    FROM `crypto-etl-bitcoin-prod.bitcoin_blockchain.outputs` as outputs
)
SELECT 
    address
,   type    
,   sum(value) as balance
FROM double_entry_book
GROUP BY 1,2
ORDER BY balance DESC
LIMIT 1000