What are the current balances of a random set of 1000 addresses on blockchain X?

bitcoin-cash.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_cash.transactions` JOIN UNNEST(inputs) AS inputs
    WHERE block_timestamp_month = '2019-01-01'

    UNION ALL
 
    -- credits
    SELECT
      ARRAY_TO_STRING(outputs.addresses, ",") AS address
    , outputs.type
    , outputs.value AS value
    FROM `bigquery-public-data.crypto_bitcoin_cash.transactions` JOIN UNNEST(outputs) AS outputs
    WHERE block_timestamp_month = '2019-01-01'
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC

bitcoin.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.transactions` JOIN UNNEST(inputs) AS inputs
    WHERE block_timestamp_month = '2019-01-01'

    UNION ALL
 
    -- credits
    SELECT
      ARRAY_TO_STRING(outputs.addresses, ",") AS address
    , outputs.type
    , outputs.value AS value
    FROM `bigquery-public-data.crypto_bitcoin.transactions` JOIN UNNEST(outputs) AS outputs
    WHERE block_timestamp_month = '2019-01-01'
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC

dash.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_dash.transactions` JOIN UNNEST(inputs) AS inputs
    WHERE block_timestamp_month = '2019-01-01'

    UNION ALL
 
    -- credits
    SELECT
      ARRAY_TO_STRING(outputs.addresses, ",") AS address
    , outputs.type
    , outputs.value AS value
    FROM `bigquery-public-data.crypto_dash.transactions` JOIN UNNEST(outputs) AS outputs
    WHERE block_timestamp_month = '2019-01-01'
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC

dogecoin.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_dogecoin.transactions` JOIN UNNEST(inputs) AS inputs
    WHERE block_timestamp_month = '2019-01-01'

    UNION ALL
 
    -- credits
    SELECT
      ARRAY_TO_STRING(outputs.addresses, ",") AS address
    , outputs.type
    , outputs.value AS value
    FROM `bigquery-public-data.crypto_dogecoin.transactions` JOIN UNNEST(outputs) AS outputs
    WHERE block_timestamp_month = '2019-01-01'
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC

erc-20.sql

WITH 
double_entry_book AS (
    -- debits
    SELECT to_address as address,  'tx' AS type, CAST(value AS NUMERIC)/POW(10,18) AS value --18 decimal places
    FROM `bigquery-public-data.crypto_ethereum.token_transfers`
    WHERE from_address IS NOT NULL AND to_address IS NOT NULL
    AND token_address = LOWER('0x408e41876cccdc0f92210600ef50372656052a38') --OMG
    AND EXTRACT(DATE FROM block_timestamp) = '2019-01-01'
    
    UNION ALL
    
    -- credits
    SELECT from_address as address, 'tx' AS type, -CAST(value AS NUMERIC)/POW(10,18) AS value --18 decimal places
    FROM `bigquery-public-data.crypto_ethereum.token_transfers`
    WHERE from_address IS NOT NULL AND to_address IS NOT NULL
    AND token_address = LOWER('0x408e41876cccdc0f92210600ef50372656052a38') --OMG
    AND EXTRACT(DATE FROM block_timestamp) = '2019-01-01'
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC

ethereum-classic.sql

WITH 
double_entry_book AS (
    -- debits
    SELECT to_address AS address, 'tx' AS type, value AS value
    FROM `bigquery-public-data.crypto_ethereum_classic.traces`
    WHERE to_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    AND EXTRACT(DATE FROM block_timestamp) = '2019-01-01' 
    
    UNION ALL
    
    -- credits
    SELECT from_address as address, 'tx' AS type, -value AS value
    FROM `bigquery-public-data.crypto_ethereum_classic.traces`
    WHERE from_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    AND EXTRACT(DATE FROM block_timestamp) = '2019-01-01' 

    UNION ALL

    -- transaction fees debits
    SELECT miner AS address, 'tx' AS type, SUM(CAST(receipt_gas_used AS NUMERIC) * CAST(gas_price AS NUMERIC)) as value
    FROM `bigquery-public-data.crypto_ethereum_classic.transactions` AS transactions
    JOIN `bigquery-public-data.crypto_ethereum_classic.blocks` AS blocks ON blocks.number = transactions.block_number
    WHERE EXTRACT(DATE FROM block_timestamp) = '2019-01-01' 
    GROUP BY blocks.miner, block_timestamp
    
    UNION ALL
    
    -- transaction fees credits
    SELECT from_address AS address, 'tx' AS type, -(CAST(receipt_gas_used AS NUMERIC) * CAST(gas_price AS NUMERIC)) AS value
    FROM `bigquery-public-data.crypto_ethereum_classic.transactions`
    WHERE EXTRACT(DATE FROM block_timestamp) = '2019-01-01' 
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC

ethereum.sql

WITH 
double_entry_book AS (
    -- debits
    SELECT to_address AS address, 'tx' AS type, value AS value
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE to_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    AND EXTRACT(DATE FROM block_timestamp) = '2019-01-01' 
    
    UNION ALL
    
    -- credits
    SELECT from_address as address, 'tx' AS type, -value AS value
    FROM `bigquery-public-data.crypto_ethereum.traces`
    WHERE from_address IS NOT NULL
    AND status = 1
    AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS NULL)
    AND EXTRACT(DATE FROM block_timestamp) = '2019-01-01' 

    UNION ALL

    -- transaction fees debits
    SELECT miner AS address, 'tx' AS type, SUM(CAST(receipt_gas_used AS NUMERIC) * CAST(gas_price AS NUMERIC)) as value
    FROM `bigquery-public-data.crypto_ethereum.transactions` AS transactions
    JOIN `bigquery-public-data.crypto_ethereum.blocks` AS blocks ON blocks.number = transactions.block_number
    WHERE EXTRACT(DATE FROM block_timestamp) = '2019-01-01' 
    GROUP BY blocks.miner, block_timestamp
    
    UNION ALL
    
    -- transaction fees credits
    SELECT from_address AS address, 'tx' AS type, -(CAST(receipt_gas_used AS NUMERIC) * CAST(gas_price AS NUMERIC)) AS value
    FROM `bigquery-public-data.crypto_ethereum.transactions`
    WHERE EXTRACT(DATE FROM block_timestamp) = '2019-01-01' 
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC

litecoin.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_litecoin.transactions` JOIN UNNEST(inputs) AS inputs
    WHERE block_timestamp_month = '2019-01-01'

    UNION ALL
 
    -- credits
    SELECT
      ARRAY_TO_STRING(outputs.addresses, ",") AS address
    , outputs.type
    , outputs.value AS value
    FROM `bigquery-public-data.crypto_litecoin.transactions` JOIN UNNEST(outputs) AS outputs
    WHERE block_timestamp_month = '2019-01-01'
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC

zcash.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_zcash.transactions` JOIN UNNEST(inputs) AS inputs
    WHERE block_timestamp_month = '2019-01-01'

    UNION ALL
 
    -- credits
    SELECT
      ARRAY_TO_STRING(outputs.addresses, ",") AS address
    , outputs.type
    , outputs.value AS value
    FROM `bigquery-public-data.crypto_zcash.transactions` JOIN UNNEST(outputs) AS outputs
    WHERE block_timestamp_month = '2019-01-01'
)
SELECT 
  address
, type    
, SUM(value) AS net_change
FROM double_entry_book
GROUP BY 1,2
ORDER BY net_change ASC