bqml_miner_feature_vectors.sql

WITH output_stats AS (
  SELECT
      ARRAY_TO_STRING(outputs.addresses,',') AS output_address
  ,   block_timestamp AS output_block_time
  ,   DATE(EXTRACT(YEAR FROM block_timestamp), EXTRACT(MONTH FROM block_timestamp), 1) as output_block_month
  ,   LAG(block_timestamp) OVER (PARTITION BY ARRAY_TO_STRING(outputs.addresses,',') ORDER BY block_timestamp) AS output_prev_block_time    
  ,   value
  ,   transaction_hash
  FROM `crypto-etl-ethereum-dev.bitcoin_blockchain.outputs` AS outputs 
),
input_stats AS (
  SELECT
      ARRAY_TO_STRING(inputs.addresses,',') AS input_address
  ,   block_timestamp AS input_block_time
  ,   DATE(EXTRACT(YEAR FROM block_timestamp), EXTRACT(MONTH FROM block_timestamp), 1) as input_block_month
  ,   LAG(block_timestamp) OVER (PARTITION BY ARRAY_TO_STRING(inputs.addresses,',') ORDER BY block_timestamp) AS input_prev_block_time    
  ,   value
  ,   transaction_hash
  FROM `crypto-etl-ethereum-dev.bitcoin_blockchain.inputs` AS inputs    
),
output_summary AS (
  SELECT
      output_address

  ,   MIN(output_block_month) AS output_month_min
  ,   MAX(output_block_month) AS output_month_max
  ,   COUNT(DISTINCT output_block_month) AS output_active_months

  ,   COUNT(DISTINCT(transaction_hash)) AS total_output_tx
  
  ,   SUM(value) AS total_tx_output_value
  ,   AVG(value) AS mean_tx_output_value
  ,   STDDEV(value) AS stddev_tx_output_value
  ,   SUM(value)/COUNT(DISTINCT output_block_month) AS mean_monthly_output_value
  ,   COUNT(transaction_hash)/COUNT(DISTINCT output_block_month) AS mean_monthly_output_count

  ,   AVG(IF(output_prev_block_time IS NULL, NULL, UNIX_SECONDS(output_block_time) - UNIX_SECONDS(output_prev_block_time))) AS mean_output_idle_time
  ,   STDDEV(IF(output_prev_block_time IS NULL, NULL, UNIX_SECONDS(output_block_time) - UNIX_SECONDS(output_prev_block_time))) AS stddev_output_idle_time

  FROM output_stats  
  GROUP BY output_address
),
input_summary AS (
  SELECT
      input_address

  ,   MIN(input_block_month) AS input_month_min
  ,   MAX(input_block_month) AS input_month_max
  ,   COUNT(DISTINCT input_block_month) AS input_active_months

  ,   COUNT(DISTINCT(transaction_hash)) AS total_input_tx

  ,   SUM(value) AS total_tx_input_value
  ,   AVG(value) AS mean_tx_input_value
  ,   STDDEV(value) AS stddev_tx_input_value
  ,   SUM(value)/COUNT(DISTINCT input_block_month) AS mean_monthly_input_value
  ,   COUNT(transaction_hash)/COUNT(DISTINCT input_block_month) AS mean_monthly_input_count

  ,   AVG(IF(input_prev_block_time IS NULL, NULL, UNIX_SECONDS(input_block_time) - UNIX_SECONDS(input_prev_block_time))) AS mean_input_idle_time
  ,   STDDEV(IF(input_prev_block_time IS NULL, NULL, UNIX_SECONDS(input_block_time) - UNIX_SECONDS(input_prev_block_time))) AS stddev_input_idle_time

  FROM input_stats
  GROUP BY input_address
)
SELECT 
    output_address
  , output_month_min
  , output_month_max
  , output_active_months
  , total_tx_output_value
  , mean_tx_output_value
  , stddev_tx_output_value
  , total_output_tx
  , mean_monthly_output_value
  , mean_monthly_output_count
  , mean_output_idle_time
  , stddev_output_idle_time
  , input_month_min
  , input_month_max
  , input_active_months
  , total_tx_input_value
  , mean_tx_input_value
  , stddev_tx_input_value
  , total_input_tx
  , mean_monthly_input_value
  , mean_monthly_input_count
  , mean_input_idle_time
  , stddev_input_idle_time  
FROM input_summary, output_summary
WHERE input_summary.input_address = output_summary.output_address