Query Dogecoin balance by timestamp in BigQuery

bigquery-dogecoin-balance.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.dogecoin_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.dogecoin_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