Skip to content

Instantly share code, notes, and snippets.

@rifkiamil

rifkiamil/Simplified-relational-diagram-sql-bitcoin.sql

Last active February 12, 2021 02:14
Show Gist options
  • Save rifkiamil/04b3998edacf2dc79b26147a39033d5b to your computer and use it in GitHub Desktop.
Save rifkiamil/04b3998edacf2dc79b26147a39033d5b to your computer and use it in GitHub Desktop.
Download ZIP
Satoshi Nakamoto sent 50 BTC to Hal Finney in block 170
Raw
Simplified-relational-diagram-sql-bitcoin.sql
# Find Bitcoin transaction
SELECT
bt.hash,
bt.block_timestamp,
CAST(bi.value AS NUMERIC)/100000000 as InputValueBTC,
CAST(bo.value AS NUMERIC)/100000000 as OutputValueBTC
FROM
`bigquery-public-data.crypto_bitcoin.transactions` AS bt
left outer join (Select transaction_hash, Sum(value) as value From `bigquery-public-data.crypto_bitcoin.inputs` GROUP BY transaction_hash) AS bi ON bt.HASH = bi.transaction_hash
left outer join (Select transaction_hash, Sum(value) as value From `bigquery-public-data.crypto_bitcoin.outputs` GROUP BY transaction_hash) AS bo ON bt.HASH = bo.transaction_hash
WHERE
bt.HASH = 'f4184fc596403b9d638783cf57adfe4c75c605f6356fbc91338530e9831e9e16'
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment