rifkiamil’s gists

rifkiamil / bigquery-public-data.crypto_ethereum.blocks.QDD

Last active July 11, 2022 02:05

DATASET bigquery-public-data DB crypto_ethereum - quickdatabasediagrams

	"bigquery-public-data.crypto_ethereum.balances" as Bal
	-
	address STRING
	eth_balance NUMERIC

	"bigquery-public-data.crypto_ethereum.blocks" as B
	--
	column_name data_type
	timestamp TIMESTAMP
	number INT64

rifkiamil / sql-bitcoin_find-transcation.sql

Last active February 11, 2021 13:22

SQL Bitcoin Part 1 - Find bitcoin transaction

	SELECT bi.transaction_hash,
	bi.block_number,
	bi.block_timestamp,
	bi.addresses,
	bi.value
	FROM `bigquery-public-data.crypto_bitcoin.inputs` as bi
	WHERE bi.transaction_hash = 'f4184fc596403b9d638783cf57adfe4c75c605f6356fbc91338530e9831e9e16'

rifkiamil / sql-bitcoin-02.sql

Created February 10, 2021 14:38

SQL Bitcoin Part 1 - Find ethereum transaction

	SELECT et.hash,
	et.nonce,
	et.from_address,
	et.to_address,
	et.block_hash,
	et.block_timestamp
	FROM `bigquery-public-data.crypto_ethereum.transactions` as et
	WHERE et.hash = '0x523542d3bbca5d8fce33c933b6bf169723b49294319227e145c48e5b4ce13f11'

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

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 / test-googlebigquery.sql

Created February 12, 2021 11:12

Test Google BigQuery

SELECT CURRENT_DATETIME() as now

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 / 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 / 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-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`

Rif rifkiamil