wartstone

SELECT 
  ROUND((input_value - output_value)/ size, 0) AS fees_per_byte,
  COUNT(*) AS txn_cnt
FROM
  `bigquery-public-data.crypto_bitcoin.transactions`
WHERE TRUE
  AND block_timestamp >= '2018-01-01' 
  AND is_coinbase IS FALSE
GROUP BY 1

wartstone

SELECT
	txn_count,
	COUNT(txn_count) AS num_addresses
FROM
(
	SELECT 
	  ARRAY_TO_STRING(inputs.addresses, '') AS addresses,
	  COUNT(DISTINCT `hash`) AS txn_count
	FROM `crypto-etl-ethereum-dev.bitcoin_blockchain.transactions` AS txns
	CROSS JOIN UNNEST(txns.inputs) AS inputs

wartstone

WITH 
output_ages AS (
  SELECT
    ARRAY_TO_STRING(outputs.addresses,',') AS output_ages_address,
    MIN(block_timestamp_month) AS output_month_min,
    MAX(block_timestamp_month) AS output_month_max
  FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
  GROUP BY output_ages_address
)
,input_ages AS (