rifkiamil

Project project_name {
  database_type: 'BigQuery'
  Note: 'Football Data by https://github.com/dcaribou'
}

Table appearances {
  player_id INTEGER
  game_id INTEGER
  appearance_id STRING [pk]
  league_id STRING

rifkiamil

Project project_name {
  database_type: 'BigQuery'
  Note: 'Football data'
}

Table appearances {
  player_id INTEGER
  game_id INTEGER
  appearance_id STRING [pk]
  league_id STRING

rifkiamil

## Find Ethereum - Internal transactions - Matchs Advanced view on Etherscan.io
SELECT *
FROM  `bigquery-public-data.crypto_ethereum.traces`
WHERE DATE(block_timestamp) = "2021-02-12" 
        and trace_address IS NOT NULL
        and block_number = 11838934
ORDER BY transaction_index

rifkiamil

## Find Ethereum - Internal transactions with values - Matchs default view on Etherscan.io
SELECT *
FROM  `bigquery-public-data.crypto_ethereum.traces`
WHERE DATE(block_timestamp) = "2021-02-12" 
        and trace_address IS NOT NULL
        and block_number = 11838934
        and value > 0
ORDER BY transaction_index

rifkiamil

## Below is the query that will give you top 20 balances on Ethereum
SELECT  Address as EthAddress, 
        CAST(eth_balance as NUMERIC)/1000000000000000000 as EthBalance
FROM    `bigquery-public-data.crypto_ethereum.balances`
ORDER BY Eth_balance DESC
LIMIT 20

rifkiamil

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

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

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

rifkiamil

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

rifkiamil

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,