indexing-transactions-and-addresses.sql

###############################################
Generate unique integer index for a transaction
###############################################
SELECT  
  txns.hash as transaction_hash
, (999999 - txns.block_number) as block_index
, (999999 - ROW_NUMBER() OVER (PARTITION BY block_number ORDER BY txns.hash) + 1) as tx_index_in_block
, 1000000 * (999999 - txns.block_number) + (999999 - ROW_NUMBER() OVER (PARTITION BY block_number ORDER BY txns.hash) + 1) as unique_tx_index
FROM `bigquery-public-data.crypto_bitcoin.transactions` as txns



############################################
Generate unique integer index for an address
############################################
SELECT
    address
,   MAX(1000000 * (block_index * 1000000 + txn_index_in_block) + output_index_in_txn) as unique_address_index
FROM
(
  SELECT  
    ARRAY_TO_STRING(outputs.addresses, ',') as address
  , (999999 - outputs.block_number) as block_index -- block_index decreases with time
  , (999999 - ROW_NUMBER() OVER (PARTITION BY block_number ORDER BY outputs.transaction_hash) + 1) as txn_index_in_block -- deterministic ordering of transactions which is used to assign a number to each txn
  , (999999 - outputs.index) as output_index_in_txn -- ordering of outputs within a transaction
  FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
  WHERE block_number = 0
  LIMIT 100
)
GROUP BY 1
ORDER BY 1