Skip to content

Instantly share code, notes, and snippets.

@allenday

allenday/mining-pool-address-features.sql

Last active June 27, 2022 22:06
Show Gist options
  • Save allenday/16cf63fb6b3ed59b78903b2d414fe75b to your computer and use it in GitHub Desktop.
Save allenday/16cf63fb6b3ed59b78903b2d414fe75b to your computer and use it in GitHub Desktop.
Download ZIP
Bitcoin mining pool address signatures and statistics of their behavior over time.
Raw
mining-pool-address-features.sql
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 (
SELECT
ARRAY_TO_STRING(inputs.addresses,',') AS input_ages_address,
MIN(block_timestamp_month) AS input_month_min,
MAX(block_timestamp_month) AS input_month_max
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
GROUP BY input_ages_address
)
,output_monthly_stats AS (
SELECT
ARRAY_TO_STRING(outputs.addresses,',') AS output_monthly_stats_address,
COUNT(DISTINCT block_timestamp_month) AS output_active_months,
COUNT(outputs) AS total_tx_output_count,
SUM(value) AS total_tx_output_value,
AVG(value) AS mean_tx_output_value,
STDDEV(value) AS stddev_tx_output_value,
COUNT(DISTINCT(`hash`)) AS total_output_tx,
SUM(value)/COUNT(block_timestamp_month) AS mean_monthly_output_value,
COUNT(outputs.addresses)/COUNT(block_timestamp_month) AS mean_monthly_output_count
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
GROUP BY output_monthly_stats_address
)
,input_monthly_stats AS (
SELECT
ARRAY_TO_STRING(inputs.addresses,',') AS input_monthly_stats_address,
COUNT(DISTINCT block_timestamp_month) AS input_active_months,
COUNT(inputs) AS total_tx_input_count,
SUM(value) AS total_tx_input_value,
AVG(value) AS mean_tx_input_value,
STDDEV(value) AS stddev_tx_input_value,
COUNT(DISTINCT(`hash`)) AS total_input_tx,
SUM(value)/COUNT(block_timestamp_month) AS mean_monthly_input_value,
COUNT(inputs.addresses)/COUNT(block_timestamp_month) AS mean_monthly_input_count
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
GROUP BY input_monthly_stats_address
)
,output_idle_times AS (
SELECT
address AS idle_time_address,
AVG(idle_time) AS mean_output_idle_time,
STDDEV(idle_time) AS stddev_output_idle_time
FROM
(
SELECT
event.address,
IF(prev_block_time IS NULL, NULL, UNIX_SECONDS(block_time) - UNIX_SECONDS(prev_block_time)) AS idle_time
FROM (
SELECT
ARRAY_TO_STRING(outputs.addresses,',') AS address,
block_timestamp AS block_time,
LAG(block_timestamp) OVER (PARTITION BY ARRAY_TO_STRING(outputs.addresses,',') ORDER BY block_timestamp) AS prev_block_time
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
) AS event
WHERE block_time != prev_block_time
)
GROUP BY address
)
,input_idle_times AS (
SELECT
address AS idle_time_address,
AVG(idle_time) AS mean_input_idle_time,
STDDEV(idle_time) AS stddev_input_idle_time
FROM
(
SELECT
event.address,
IF(prev_block_time IS NULL, NULL, UNIX_SECONDS(block_time) - UNIX_SECONDS(prev_block_time)) AS idle_time
FROM (
SELECT
ARRAY_TO_STRING(inputs.addresses,',') AS address,
block_timestamp AS block_time,
LAG(block_timestamp) OVER (PARTITION BY ARRAY_TO_STRING(inputs.addresses,',') ORDER BY block_timestamp) AS prev_block_time
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
) AS event
WHERE block_time != prev_block_time
)
GROUP BY address
)
--,miners AS (
--)
SELECT
TRUE AS is_miner,
output_ages_address AS address,
UNIX_SECONDS(CAST(output_ages.output_month_min AS TIMESTAMP)) AS output_month_min,
UNIX_SECONDS(CAST(output_ages.output_month_max AS TIMESTAMP)) AS output_month_max,
UNIX_SECONDS(CAST(input_ages.input_month_min AS TIMESTAMP)) AS input_month_min,
UNIX_SECONDS(CAST(input_ages.input_month_max AS TIMESTAMP)) AS input_month_max,
UNIX_SECONDS(CAST(output_ages.output_month_max AS TIMESTAMP)) - UNIX_SECONDS(CAST(output_ages.output_month_min AS TIMESTAMP)) AS output_active_time,
UNIX_SECONDS(CAST(input_ages.input_month_max AS TIMESTAMP)) - UNIX_SECONDS(CAST(input_ages.input_month_min AS TIMESTAMP)) AS input_active_time,
UNIX_SECONDS(CAST(output_ages.output_month_max AS TIMESTAMP)) - UNIX_SECONDS(CAST(input_ages.input_month_max AS TIMESTAMP)) AS io_max_lag,
UNIX_SECONDS(CAST(output_ages.output_month_min AS TIMESTAMP)) - UNIX_SECONDS(CAST(input_ages.input_month_min AS TIMESTAMP)) AS io_min_lag,
output_monthly_stats.output_active_months,
output_monthly_stats.total_tx_output_count,
output_monthly_stats.total_tx_output_value,
output_monthly_stats.mean_tx_output_value,
output_monthly_stats.stddev_tx_output_value,
output_monthly_stats.total_output_tx,
output_monthly_stats.mean_monthly_output_value,
output_monthly_stats.mean_monthly_output_count,
input_monthly_stats.input_active_months,
input_monthly_stats.total_tx_input_count,
input_monthly_stats.total_tx_input_value,
input_monthly_stats.mean_tx_input_value,
input_monthly_stats.stddev_tx_input_value,
input_monthly_stats.total_input_tx,
input_monthly_stats.mean_monthly_input_value,
input_monthly_stats.mean_monthly_input_count,
output_idle_times.mean_output_idle_time,
output_idle_times.stddev_output_idle_time,
input_idle_times.mean_input_idle_time,
input_idle_times.stddev_input_idle_time
FROM
output_ages, output_monthly_stats, output_idle_times,
input_ages, input_monthly_stats, input_idle_times
WHERE TRUE
AND output_ages.output_ages_address = output_monthly_stats.output_monthly_stats_address
AND output_ages.output_ages_address = output_idle_times.idle_time_address
AND output_ages.output_ages_address = input_monthly_stats.input_monthly_stats_address
AND output_ages.output_ages_address = input_ages.input_ages_address
AND output_ages.output_ages_address = input_idle_times.idle_time_address
AND output_ages.output_ages_address IN
(
SELECT
ARRAY_TO_STRING(outputs.addresses,',') AS miner
FROM
`bigquery-public-data.crypto_bitcoin.blocks` AS blocks,
`bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
WHERE blocks.hash = transactions.block_hash
AND is_coinbase IS TRUE
AND ( FALSE
--
-- miner signatures from https://en.bitcoin.it/wiki/Comparison_of_mining_pools
--
OR coinbase_param LIKE '%4d696e656420627920416e74506f6f6c%' --AntPool
OR coinbase_param LIKE '%2f42434d6f6e737465722f%' --BCMonster
--BitcoinAffiliateNetwork
OR coinbase_param LIKE '%4269744d696e746572%' --BitMinter
--BTC.com
--BTCC Pool
--BTCDig
OR coinbase_param LIKE '%2f7374726174756d2f%' --Btcmp
--btcZPool.com
--BW Mining
OR coinbase_param LIKE '%456c6967697573%' --Eligius
--F2Pool
--GHash.IO
--Give Me COINS
--Golden Nonce Pool
OR coinbase_param LIKE '%2f627261766f2d6d696e696e672f%' --Bravo Mining
OR coinbase_param LIKE '%4b616e6f%' --KanoPool
--kmdPool.org
OR coinbase_param LIKE '%2f6d6d706f6f6c%' --Merge Mining Pool
--MergeMining
--Multipool
--P2Pool
OR coinbase_param LIKE '%2f736c7573682f%' --Slush Pool
--ZenPool.org
)
GROUP BY miner
HAVING COUNT(1) >= 20
)
@jeyasheelarakkini344
Copy link

what is block_time_stamp_month
what is output addresses
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment