bigquerybitcoin

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