GSP-665: Exploring the Public Cryptocurrency Datasets Available in BigQuery
Overview
Task - 5.1 : Store the transaction hash of the large mystery transfer of 194993 BTC in the table 51 inside the lab dataset:
1CREATE OR REPLACE TABLE lab.51 (transaction_hash STRING) as
2SELECT transaction_id FROM `bigquery-public-data.bitcoin_blockchain.transactions` , UNNEST( outputs ) as outputs
3where outputs.output_satoshis = 19499300000000
Task - 5.2 : Store the balance of the pizza purchase address in the table 52 inside the lab dataset:
1-- SQL source from https://cloud.google.com/blog/product...
2CREATE OR REPLACE TABLE lab.52 (balance NUMERIC) as
3WITH double_entry_book AS (
4 -- debits
5 SELECT
6 array_to_string(inputs.addresses, ",") as address
7 , -inputs.value as value
8 FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
9 UNION ALL
10 -- credits
11 SELECT
12 array_to_string(outputs.addresses, ",") as address
13 , outputs.value as value
14 FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
15
16)
17SELECT
18sum(value) as balance
19FROM double_entry_book
20where address = "1XPTgDRhN8RFnzniWCddobD9iKZatrvH4"
Congratulations, you're all done with the lab 😄