In May 2021 https://cloud.google.com/bigquery/docs/release-notes#May_10_2021 we got this feature for Google BigQuery https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#pivot_operator
In regards to bigquery-public-data:crypto_ethereum.token_transfers "The most popular type of transaction on the Ethereum blockchain invokes a contract of type ERC20 to perform a transfer operation, moving some number of tokens from one 20-byte address to another 20-byte address. This table contains the subset of those transactions and has further processed and denormalized the data to make it easier to consume for analysis of token transfer events." Ethereum ETL opensource code would cover any other items. Does that help? Do you have any other questions?
Find a simplified relational diagram for the Bitcoin data coming from an amazing opensource project https://github.com/blockchain-etl/bitcoin-etl.
Keeping cost low
- When working with crypto_bitcoin.blocks and crypto_bitcoin.transactions try to use partitioned field block_timestamp_month. This will speed up your queries and reduce your costs.
WHERE transactions.block_timestamp_month = '2021-02-01'WHERE blocks.block_timestamp_month = '2010-12-01'