ethereum_burns.sql

-- Pads indexes in trace_address to 5 so trace addresses can be chronologically ordered
CREATE TEMPORARY FUNCTION normalize_trace_address(trace_address STRING)
    RETURNS STRING AS ((
    SELECT ARRAY_TO_STRING(ARRAY_AGG(lpadded), ',') as arr
    FROM
    (
      SELECT LPAD(addr, 5, '0') as lpadded
      FROM UNNEST((
        SELECT SPLIT(trace_address, ','))
      ) AS addr
    )
));


select traces2.block_timestamp, traces2.transaction_hash, traces2.value, traces1.trace_address as ta1, traces2.trace_address as ta2
from `bigquery-public-data.crypto_ethereum.traces` as traces1
join `bigquery-public-data.crypto_ethereum.traces` as traces2 using(transaction_hash)
  where traces1.trace_type = 'suicide'
  and traces1.from_address = traces2.to_address
  and normalize_trace_address(traces2.trace_address) > normalize_trace_address(traces1.trace_address)
--   and date(traces1.block_timestamp) = '2016-12-30'
--   and date(traces2.block_timestamp) = '2016-12-30'
  and traces2.value > 0
  
limit 1000