Skip to content

Instantly share code, notes, and snippets.

@allenday

allenday/transacting-partner-count.sql

Created January 25, 2019 08:27
Show Gist options
  • Save allenday/41d3a027f7dc01e169e25f222a4fdd68 to your computer and use it in GitHub Desktop.
Save allenday/41d3a027f7dc01e169e25f222a4fdd68 to your computer and use it in GitHub Desktop.
Download ZIP
Number of transacting partners per Bitcoin address
Raw
transacting-partner-count.sql
SELECT
txn_count,
COUNT(txn_count) AS num_addresses
FROM
(
SELECT
ARRAY_TO_STRING(inputs.addresses, '') AS addresses,
COUNT(DISTINCT `hash`) AS txn_count
FROM `crypto-etl-ethereum-dev.bitcoin_blockchain.transactions` AS txns
CROSS JOIN UNNEST(txns.inputs) AS inputs
GROUP BY addresses
)
GROUP BY txn_count
ORDER BY txn_count ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment