rifkiamil’s gists

rifkiamil / gist:f436f3ffab44a3b9ea359715bc8e8d26

Created February 17, 2021 17:48

crypto_bitcoin (full)

	//// Advanced relational diagram for bigquery-public-data:crypto_bitcoin
	//// Update on 2021-02-12
	//// diagram by @rifkiamil
	//// For the https://github.com/blockchain-etl/bitcoin-etl project

	Project "bigquery-public-data:Crypto_Bitcoin" {
	database_type: 'BigQuery'
	Note: 'Advanced Bitcoin diagram layout (not using RECORD field type)'
	}

rifkiamil / sql-bitcoin-05.sql

Created February 17, 2021 00:37

SQL Bitcoin Part 2 - WHERE block_timestamp_month = Dec 2013 and Bitcoin Value more then 1 Bitcoin and hash transcation is e7f...fd7

	SELECT block_timestamp,
	block_timestamp_month,
	output_value
	FROM `bigquery-public-data.crypto_bitcoin.transactions`
	WHERE block_timestamp_month = '2013-12-01'
	and output_value > 10000000
	and `hash` = 'e7f8fe18fdc039535fef6d3e82fc086cca2e50d2e1f7f68ab1e97ee8dcafdfd7'

rifkiamil / sql-bitcoin-04.sql

Created February 15, 2021 16:27

SQL Bitcoin Part 2 - Find transactions with block_timestamp, block_timestamp_month, output_value WHERE block_timestamp_month

	SELECT block_timestamp, block_timestamp_month, output_value
	FROM `bigquery-public-data.crypto_bitcoin.transactions`
	WHERE block_timestamp_month = '2013-12-01'

rifkiamil / sql-bitcoin-03.sql

Created February 15, 2021 16:24

SQL Bitcoin Part 2 - Find transactions with block_timestamp, block_timestamp_month, output_value

	SELECT block_timestamp, block_timestamp_month, output_value
	FROM `bigquery-public-data.crypto_bitcoin.transactions`

rifkiamil / Full relational diagram - sql with partitioned field.sql

Created February 12, 2021 17:09

Full relational diagram - sql with partitioned field

	SELECT
	transactions.hash as transaction_hash,
	transactions.block_hash,
	transactions.block_number,
	transactions.block_timestamp,
	outputs.index,
	outputs.script_asm,
	outputs.script_hex,
	outputs.required_signatures,
	outputs.type,

rifkiamil / sql-for-crypto_bitcoin.outputs.sql

Created February 12, 2021 16:30

SQL for crypto_bitcoin.outputs

	SELECT
	transactions.hash as transaction_hash,
	transactions.block_hash,
	transactions.block_number,
	transactions.block_timestamp,
	outputs.index,
	outputs.script_asm,
	outputs.script_hex,
	outputs.required_signatures,
	outputs.type,

rifkiamil / sql-for-crypto_bitcoin.inputs.sql

Created February 12, 2021 16:29

SQL for crypto_bitcoin.inputs

	SELECT
	transactions.hash as transaction_hash,
	transactions.block_hash,
	transactions.block_number,
	transactions.block_timestamp,
	inputs.index,
	inputs.spent_transaction_hash,
	inputs.spent_output_index,
	inputs.script_asm,
	inputs.script_hex,

rifkiamil / test-googlebigquery.sql

Created February 12, 2021 11:12

Test Google BigQuery

SELECT CURRENT_DATETIME() as now

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

Last active February 12, 2021 02:14

Satoshi Nakamoto sent 50 BTC to Hal Finney in block 170

	# 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

rifkiamil / sql-dogecoin_find-transcation.sql

Created February 11, 2021 13:24

SQL Bitcoin Part 1 - Find dogecoin transaction

	SELECT di.transaction_hash,
	di.block_number,
	di.block_timestamp,
	di.addresses,
	di.value
	FROM `bigquery-public-data.crypto_dogecoin.inputs` as di
	WHERE di.transaction_hash = 'c2941b764e5436aa4bb39eaa1360cf7156f80d831141c0d352e950d7f2427325'
	LIMIT 1000

Rif rifkiamil