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

  • Select an option

    Learn more about clone URLs
  • Save allenday/16cf63fb6b3ed59b78903b2d414fe75b to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save allenday/16cf63fb6b3ed59b78903b2d414fe75b to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. allenday revised this gist Jan 25, 2019. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions mining-pool-address-features.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -133,8 +133,8 @@ WHERE TRUE
    SELECT
    ARRAY_TO_STRING(outputs.addresses,',') AS miner
    FROM
    `crypto-etl-bitcoin-prod.crypto_bitcoin.blocks` AS blocks,
    `crypto-etl-bitcoin-prod.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
    `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
  2. allenday revised this gist Jan 23, 2019. 1 changed file with 6 additions and 6 deletions.
    12 changes: 6 additions & 6 deletions mining-pool-address-features.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -4,15 +4,15 @@ output_ages AS (
    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 `crypto-etl-bitcoin-prod.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
    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 `crypto-etl-bitcoin-prod.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
    GROUP BY input_ages_address
    )
    ,output_monthly_stats AS (
    @@ -26,7 +26,7 @@ output_ages AS (
    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 `crypto-etl-bitcoin-prod.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
    GROUP BY output_monthly_stats_address
    )
    ,input_monthly_stats AS (
    @@ -40,7 +40,7 @@ output_ages AS (
    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 `crypto-etl-bitcoin-prod.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
    GROUP BY input_monthly_stats_address
    )
    ,output_idle_times AS (
    @@ -58,7 +58,7 @@ output_ages AS (
    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 `crypto-etl-bitcoin-prod.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(outputs) AS outputs
    ) AS event
    WHERE block_time != prev_block_time
    )
    @@ -79,7 +79,7 @@ output_ages AS (
    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 `crypto-etl-bitcoin-prod.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS transactions JOIN UNNEST(inputs) AS inputs
    ) AS event
    WHERE block_time != prev_block_time
    )
  3. allenday created this gist Jan 23, 2019.
    171 changes: 171 additions & 0 deletions mining-pool-address-features.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,171 @@
    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 `crypto-etl-bitcoin-prod.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 `crypto-etl-bitcoin-prod.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 `crypto-etl-bitcoin-prod.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 `crypto-etl-bitcoin-prod.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 `crypto-etl-bitcoin-prod.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 `crypto-etl-bitcoin-prod.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
    `crypto-etl-bitcoin-prod.crypto_bitcoin.blocks` AS blocks,
    `crypto-etl-bitcoin-prod.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
    )