bqml_normalized_miner_features.sql

WITH 
input_metrics AS (
	SELECT
		ARRAY_TO_STRING(inputs.addresses,',') AS address, 
		SUM(inputs.value) as value,
		COUNT(inputs.value) as cnt,
		AVG(inputs.value) as mean,
		STDDEV(inputs.value) as std_dev
	FROM `crypto-etl-ethereum-dev.bitcoin_blockchain.transactions` AS transactions JOIN UNNEST(transactions.inputs) AS inputs
	GROUP BY 1
)
, output_metrics AS (
	SELECT
		ARRAY_TO_STRING(outputs.addresses,',') AS address, 
		SUM(outputs.value) as value,
		COUNT(outputs.value) as cnt,
		AVG(outputs.value) as mean,
		STDDEV(outputs.value) as std_dev,
		DATE_DIFF(MAX(DATE(block_timestamp)), MIN(DATE(block_timestamp)), DAY) + 1 AS life_outputs,
		COUNT(DISTINCT(DATE(block_timestamp))) AS num_days_active
	FROM `crypto-etl-ethereum-dev.bitcoin_blockchain.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
	GROUP BY 1
),
labels AS (
	SELECT 
		address, 
		is_miner 
	FROM `bitcoin_blockchain.bqml_miner_vectors_test`
)
SELECT
	input_metrics.address,
  is_miner,
	IF(input_metrics.value / output_metrics.value IS NULL, 0, input_metrics.value / output_metrics.value) as distribution_vol_pct,
	IF(input_metrics.cnt / output_metrics.cnt IS NULL, 0, input_metrics.cnt / output_metrics.cnt) as distribution_cnt_pct,
	output_metrics.mean / 1e8 as avg_output_value,
	output_metrics.std_dev / 1e8 as std_dev_output_value,
	num_days_active / life_outputs  as days_active_pct
FROM input_metrics, output_metrics, labels
WHERE input_metrics.address = output_metrics.address AND labels.address = input_metrics.address
AND output_metrics.cnt >= 20