bq_queries.sql

################################################################################
1. Find number of addresses sending funds to an address
################################################################################

SELECT 
    COUNT(DISTINCT ARRAY_TO_STRING(inputs.addresses, '')) AS addresses
,   MIN(block_timestamp) as start_date
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
    ,   UNNEST(txns.outputs) AS outputs
    ,   UNNEST(txns.inputs) AS inputs
WHERE 
    ARRAY_TO_STRING(outputs.addresses, '')  IN ('17A16QmavnUfCW11DAApiJxp7ARnxN5pGX', '12cgpFdJViXbwHbhrA3TuW1EGnL25Zqc3P', '374CkpmZpMBGHuQKPityryi7u5Ra1jNUvM', '1L6zTihRVecCjisYkn6BuXKrwvg8hJFC4f')


################################################################################
2. Find number of addresses which have shared their 
    inputs with an address in the same txn
################################################################################

SELECT
    COUNT(DISTINCT ARRAY_TO_STRING(inputs.addresses, ''))
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, '') = ('17oqdUbQWHasxeAkj1jzwridNZ6FPjBfdb', '1HCviLYNqHAyeZxGTj9Mtgvj1NJgQuSo91', '1Gn8WVwCGtwWEMKv9oxfNpCmSj8GTY2TaT')
)   

################################################################################
3. Number of unique withdrawal addresses
################################################################################

SELECT
    COUNT(DISTINCT ARRAY_TO_STRING(outputs.addresses, ''))
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
    WHERE 
        ARRAY_TO_STRING(inputs.addresses, '') = ('17oqdUbQWHasxeAkj1jzwridNZ6FPjBfdb', '1HCviLYNqHAyeZxGTj9Mtgvj1NJgQuSo91', '1Gn8WVwCGtwWEMKv9oxfNpCmSj8GTY2TaT')
)


################################################################################
4. Number of unique addresses depositing into exchange deposit wallet
################################################################################
SELECT
    count(DISTINCT ARRAY_TO_STRING(inputs.addresses, '')) 
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
CROSS JOIN UNNEST(txns.outputs) AS outputs
CROSS JOIN UNNEST(txns.inputs) AS inputs
WHERE ARRAY_TO_STRING(outputs.addresses, '') IN
(
    SELECT 
        ARRAY_TO_STRING(inputs.addresses, '') AS address
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
    CROSS JOIN UNNEST(txns.outputs) AS outputs
    CROSS JOIN UNNEST(txns.inputs) AS inputs
    WHERE 
        ARRAY_TO_STRING(outputs.addresses, '') = ('17oqdUbQWHasxeAkj1jzwridNZ6FPjBfdb', '1HCviLYNqHAyeZxGTj9Mtgvj1NJgQuSo91', '1Gn8WVwCGtwWEMKv9oxfNpCmSj8GTY2TaT')
)


################################################################################
5. Number of addresses with more than `x` txns made to exchange deposit wallet
################################################################################
SELECT
  COUNT(address)
FROM
(
  SELECT 
      ARRAY_TO_STRING(inputs.addresses, '') AS address, COUNT(*) as cnt_1, COUNT(DISTINCT txns.hash) AS cnt_2
  FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
  CROSS JOIN UNNEST(txns.outputs) AS outputs
  CROSS JOIN UNNEST(txns.inputs) AS inputs
  WHERE 
      ARRAY_TO_STRING(outputs.addresses, '') = ('17oqdUbQWHasxeAkj1jzwridNZ6FPjBfdb', '1HCviLYNqHAyeZxGTj9Mtgvj1NJgQuSo91', '1Gn8WVwCGtwWEMKv9oxfNpCmSj8GTY2TaT')
  GROUP BY 1 
  HAVING cnt_2 >= 10
)

################################################################################
6. Intersect between withdrawal addresses and real deposit addresses
################################################################################
SELECT 
    COUNT(DISTINCT ADDRESS)
FROM
(
    SELECT
        ARRAY_TO_STRING(inputs.addresses, '') AS ADDRESS
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
    CROSS JOIN UNNEST(txns.outputs) AS outputs
    CROSS JOIN UNNEST(txns.inputs) AS inputs
    WHERE ARRAY_TO_STRING(outputs.addresses, '') IN
    (
        SELECT 
            ARRAY_TO_STRING(inputs.addresses, '') AS address
        FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
        CROSS JOIN UNNEST(txns.outputs) AS outputs
        CROSS JOIN UNNEST(txns.inputs) AS inputs
        WHERE 
            ARRAY_TO_STRING(outputs.addresses, '') = ('17oqdUbQWHasxeAkj1jzwridNZ6FPjBfdb', '1HCviLYNqHAyeZxGTj9Mtgvj1NJgQuSo91', '1Gn8WVwCGtwWEMKv9oxfNpCmSj8GTY2TaT')
    )
    GROUP BY 1

    INTERSECT DISTINCT

    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
        WHERE 
            ARRAY_TO_STRING(inputs.addresses, '') = ('17oqdUbQWHasxeAkj1jzwridNZ6FPjBfdb', '1HCviLYNqHAyeZxGTj9Mtgvj1NJgQuSo91', '1Gn8WVwCGtwWEMKv9oxfNpCmSj8GTY2TaT')
    )
    GROUP BY 1
)    


################################################################################
7. Union of all addresses depositing to hot wallet and addresses which
have shared input with hot wallet
################################################################################

SELECT
    COUNT(DISTINCT address)
FROM
(
    SELECT
       ARRAY_TO_STRING(inputs.addresses, '') AS address
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
    CROSS JOIN 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 ('17A16QmavnUfCW11DAApiJxp7ARnxN5pGX', '12cgpFdJViXbwHbhrA3TuW1EGnL25Zqc3P', '374CkpmZpMBGHuQKPityryi7u5Ra1jNUvM', '1L6zTihRVecCjisYkn6BuXKrwvg8hJFC4f')
    )
    GROUP BY 1

    UNION DISTINCT

    SELECT
       ARRAY_TO_STRING(inputs.addresses, '') AS address
    FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
    CROSS JOIN UNNEST(txns.outputs) AS outputs
    CROSS JOIN UNNEST(txns.inputs) AS inputs
    WHERE
       ARRAY_TO_STRING(outputs.addresses, '')  IN ('17A16QmavnUfCW11DAApiJxp7ARnxN5pGX', '12cgpFdJViXbwHbhrA3TuW1EGnL25Zqc3P', '374CkpmZpMBGHuQKPityryi7u5Ra1jNUvM', '1L6zTihRVecCjisYkn6BuXKrwvg8hJFC4f')
    GROUP BY 1
)


################################################################################
8. Multi input single output
################################################################################


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 ('17A16QmavnUfCW11DAApiJxp7ARnxN5pGX', '12cgpFdJViXbwHbhrA3TuW1EGnL25Zqc3P', '374CkpmZpMBGHuQKPityryi7u5Ra1jNUvM', '1L6zTihRVecCjisYkn6BuXKrwvg8hJFC4f')
) AND output_count = 1
GROUP BY 1






SELECT
   COUNT(DISTINCT ARRAY_TO_STRING(inputs.addresses, '')) AS address
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
CROSS JOIN UNNEST(txns.inputs) AS inputs
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
WHERE ARRAY_TO_STRING(inputs.addresses, '') IN
    (
        SELECT
           ARRAY_TO_STRING(inputs.addresses, '') AS address
        FROM `bigquery-public-data.crypto_bitcoin.transactions` AS txns
        CROSS JOIN 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
             CROSS JOIN UNNEST(txns.outputs) AS outputs         
             WHERE
                 ARRAY_TO_STRING(inputs.addresses, '')  IN ('17A16QmavnUfCW11DAApiJxp7ARnxN5pGX', '12cgpFdJViXbwHbhrA3TuW1EGnL25Zqc3P', '374CkpmZpMBGHuQKPityryi7u5Ra1jNUvM', '1L6zTihRVecCjisYkn6BuXKrwvg8hJFC4f')
        ) AND output_count = 1
        GROUP BY 1
    )
)    
OR ARRAY_TO_STRING(outputs.addresses, '')  IN ('17A16QmavnUfCW11DAApiJxp7ARnxN5pGX', '12cgpFdJViXbwHbhrA3TuW1EGnL25Zqc3P', '374CkpmZpMBGHuQKPityryi7u5Ra1jNUvM', '1L6zTihRVecCjisYkn6BuXKrwvg8hJFC4f')