who-knows-what-this-does.sql

WITH shared_inputs AS (
    SELECT
        ARRAY_TO_STRING(inputs.addresses, '') AS address
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
        ,   UNNEST(txns.inputs) AS inputs
    WHERE txns.hash IN 
    (
        SELECT 
            txns.hash as tx_hash
        FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
        CROSS JOIN UNNEST(txns.inputs) AS inputs
        WHERE 
            ARRAY_TO_STRING(inputs.addresses, '') IN (SELECT address FROM `bitcoin-etl.clusttering.iteration_3`)
    )
    GROUP BY 1
),
miso AS (
    SELECT
       ARRAY_TO_STRING(outputs.addresses, '') AS address
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
    CROSS JOIN UNNEST(txns.outputs) AS outputs
    WHERE txns.hash IN
    (
         SELECT
             txns.hash as tx_hash
         FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
         CROSS JOIN UNNEST(txns.inputs) AS inputs
         CROSS JOIN UNNEST(txns.outputs) AS outputs         
         WHERE
             ARRAY_TO_STRING(inputs.addresses, '')  IN (SELECT address FROM shared_inputs)
    ) AND output_count = 1
    GROUP BY 1
),
intersection AS (
    SELECT
        miso.address
    FROM shared_inputs 
    INNER join miso
    ON shared_inputs.address = miso.address
),
    SELECT
        COUNT(DISTINCT ARRAY_TO_STRING(inputs.addresses, '')) AS address
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
        ,   UNNEST(txns.inputs) AS inputs
    WHERE txns.hash IN 
    (
        SELECT 
            txns.hash as tx_hash
        FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
        CROSS JOIN UNNEST(txns.inputs) AS inputs
        WHERE 
            ARRAY_TO_STRING(inputs.addresses, '') IN (SELECT address FROM shared_inputs)
    )
    GROUP BY 1