Скачать или смотреть GSP665 Exploring the Public Cryptocurrency Datasets Available in BigQuery

Google Cloud Self-Paced Labs

Overview
This lab lets you explore the six cryptocurrency blockchain datasets released publically in BigQuery. This was introduced in the blog post Introducing six new cryptocurrencies in BigQuery Public Datasets—and how to analyze them.

Note: This is a challenge lab. This means you will not be given all the parts to tasks that are marked. You must have working knowledge of SQL.

Maybe this query can help you, good luck :
=================================
Double-entry book query of Dogecoin
-----------------------------------------------------------------
-- SQL source from https://cloud.google.com/blog/product...
WITH double_entry_book AS (
-- debits
SELECT
array_to_string(inputs.addresses, ",") as address
, inputs.type
, -inputs.value as value
FROM `bigquery-public-data.crypto_dogecoin.inputs` as inputs
UNION ALL
-- credits
SELECT
array_to_string(outputs.addresses, ",") as address
, outputs.type
, outputs.value as value
FROM `bigquery-public-data.crypto_dogecoin.outputs` as outputs
)
SELECT
address
, type
, sum(value) as balance
FROM double_entry_book
GROUP BY 1,2
ORDER BY balance DESC
LIMIT 100

================================
transaction hash query
-------------------------------------
CREATE OR REPLACE TABLE lab.51 (transaction_hash STRING) as
SELECT transaction_id FROM `bigquery-public-data.bitcoin_blockchain.transactions` , UNNEST( outputs ) as outputs
where outputs.output_satoshis = 19499300000000

==================================
Calculate balance query
-----------------------------------------
-- SQL source from https://cloud.google.com/blog/product...
CREATE OR REPLACE TABLE lab.52 (balance NUMERIC) as
WITH double_entry_book AS (
-- debits
SELECT
array_to_string(inputs.addresses, ",") as address
, -inputs.value as value
FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
UNION ALL
-- credits
SELECT
array_to_string(outputs.addresses, ",") as address
, outputs.value as value
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs

)
SELECT
sum(value) as balance
FROM double_entry_book
where address = "1XPTgDRhN8RFnzniWCddobD9iKZatrvH4"

==================================