Skip to content

Instantly share code, notes, and snippets.

@aknirmal90
Last active June 27, 2019 10:30
Show Gist options
  • Save aknirmal90/ac78e068d2bcf14a6c0176896aa89049 to your computer and use it in GitHub Desktop.
Save aknirmal90/ac78e068d2bcf14a6c0176896aa89049 to your computer and use it in GitHub Desktop.
################################################################################
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')
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment