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 Ethereum data coming from an amazing opensource project https://github.com/blockchain-etl. The full Ethereum diagram can be used if you want more details on the trace table and internal transactions. You can also find a simplified and full diagram for Bitcoin ₿.

In Part 1, we ran two different SQL queries on real databases and experienced the realities of working with large amounts of data. What I will cover in Part 2: Databases terms using a spreadsheet analogy Write our first SQL query to get Bitcoin data. Understanding SELECT, FROM and WHERE clause in SQL Databases terms using a spreadsheet analogy You will find a…

Why Learn SQL? Structured Query Language (SQL) is a language that allows you to communicate with and manipulate databases. SQL has been around for over 40 years; its the de facto language of people that work with data. Why Learn SQL with Bitcoin? When learning something new, it can often be best to find a catalyst or real-world…

Find a full relational diagram for the Bitcoin data coming from an amazing opensource project https://github.com/blockchain-etl/bitcoin-etl. If you prefer to deal with star or snowflake schema more check out my simplified relational diagram. Two RECORDS type fields in crypto_bitcoin.transactions As each Bitcoin transaction can move money from multiple wallets (inputs) to multiple wallets (outputs) we need…

Guide to setup BigQuery without a credit card. Great news for billions of people that don’t own a credit card, you can now access one of the most powerful databases on the internet. A step by step video guide can also be found on youtube. Step 1 - Go to https://console.cloud.google.com/bigquery Always use the following URL…

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'

I just got back from China where I got to meet 14 hardware startups as well as HighWay1: a company that’s helping them turn their prototypes into products. …