Skip to content

Instantly share code, notes, and snippets.

@rvrsh3ll
Last active Aug 17, 2021
Embed
What would you like to do?
Google BigQuery Bitcoin Addresses
WITH double_entry_book AS (
-- debits
SELECT
array_to_string(inputs.addresses, ",") as address
, -inputs.value as value
, block_timestamp
FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
UNION ALL
-- credits
SELECT
array_to_string(outputs.addresses, ",") as address
, outputs.value as value
, block_timestamp
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
)
SELECT
address
, block_timestamp
, sum(value) as balance
FROM double_entry_book WHERE DATE(block_timestamp) >= '2011-1-1' and DATE(block_timestamp) <= '2013-1-1' AND value > 1000000000
GROUP BY 1,2
ORDER BY balance DESC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment