Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
with double_entry_book as (
-- debits
SELECT
array_to_string(inputs.addresses, ",") as address
, block_timestamp
, -inputs.value as value
FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
WHERE DATE(block_timestamp) >= '2011-1-1' and DATE(block_timestamp) <= '2014-1-1'
UNION ALL
-- credits
SELECT
array_to_string(outputs.addresses, ",") as address
, block_timestamp
, outputs.value as value
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
WHERE DATE(block_timestamp) >= '2011-1-1' and DATE(block_timestamp) <= '2014-1-1'
)
select address, sum(value)/100000000 as balance
from double_entry_book WHERE address LIKE '1%'
group by address
having balance>0.5
order by balance desc
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment