We will be using Dune Analytics to query the blockchain data and create the charts.

Let's navigate to Dune query window.

![Dune setup](/images/tasks/setup_dune.png) Dune Query Window

We will look at transactions created so far in Ethereum network.

#### What is a Transaction?

Ethereum is a global computer that executes smart contract and stores its data (generally referred as contract state).

In addition to smart contract's state, it also stores account balances i.e ETH owned by accounts.

![Ethereum global state](/images/tasks/contract_state.png)Ethereum global state

Ethereum's global state is updated on the following three scenarios.

**Transaction Type I : Create Smart Contract** 

To create a smart contract on the Ethereum network, the user signs and sends a transaction along with the code. Once the transaction is processed, the Ethereum Virtual Machine (also known as the EVM) updates the global state to store the new contract's code and data.

<video src="/images/tasks/contract_creation_animation.mp4">
</video>

**Transaction Type II : Call Smart Contract** 

In order to perform action on an existing contract, the user signs and sends a transaction to invoke the required method along with the input data. Upon processing, the EVM updates the contract's data to reflect the current state. 

For instance, when a user sells their NFT registered in a particular contract, the contract's state will be updated to reflect the new owner.

<video src="/images/tasks/existing_contract_animation.mp4">
</video>

**Transaction Type III : Transfer ETH** 

When a user transfers ETH to another user, the account state is updated to reflect the new balances.

<video src="/images/tasks/eth_transfer_animation.mp4">
</video>

As we've seen, any changes to the Ethereum network are made through transactions. Transactions are the means by which external users interact with the system. So, by measuring the transaction volume, we can observe how active the network is.

Since we have observed that every transaction is recorded in the blocks (referred to as the blockchain), we can figure out the total number of transactions by querying the blocks.

Dune Analytics extracts the transaction data from the blocks and loads them into the ```ethereum.transactions``` table.

![Host blockchain data](/images/tasks/dune_transactions.png)

To identify the total number of transactions since inception, we can just count the total number of rows in the table.

```
/* Query to retrieve total number of transactions in Ethereum */
SELECT
 --Divided by 1e9 to report results in billions
 COUNT(*)/1e9 AS transactions_count
FROM
 ethereum.transactions;
```

👨🏽‍💻 Now paste the above query into your Dune window and execute it. It may take some time to produce the results.

Once executed, We can create a counter chart to show the results.

![Steps to create counter chart in Dune](/images/tasks/dune_setup_scroller_6.png)Steps to create counter chart

Great work! We explored transactions and created our first query to extract data from the public blockchain.

List Ethereum transactions

List Ethereum Transactions using Dune Analytics


Most of us organize our data using spreadsheets. It works well in most simple cases, but not in complex systems.
Many organizations rely on relational databases to store and retrieve large amounts of data.

A relational database stores data in the form of tables. Tables organizes information into rows and columns. Tables can have relationships with one another.

If you have used a spreadsheet, you can assume that

- spreadsheet ~ database.
- tabs ~ tables.

![Databases understand SQL](/images/tasks/why_sql.png)SQL is a language to communicate with database.

We can express our requirements in SQL, and the database engine will retrieve the data in the requested format.

Consider a simple tracker that records all expenses. Assume that a relational database is used to store the data.

![Project example](/images/tasks/tracker_db_load.png) Sample data for the below exercise.

Let us write some simple SQL queries to analyze the data.

#### Query 1 : Basic Syntax

Now on to the basic syntax to retrieve all the data from the ```expenses``` table.

![SQL Query 1](/images/tasks/query_1.png)SQL [playground](https://www.db-fiddle.com/f/ayVhtzzdadCPS2Lzpr3EFy/19)

![SQL Query 1 Results](/images/tasks/query_1_results.png)Results from the database based on the above SQL

#### Query 2 : WHERE clause

Here's how to add a condition to filter only required rows.

![SQL Query 2](/images/tasks/query_2.png)SQL [playground](https://www.db-fiddle.com/f/ayVhtzzdadCPS2Lzpr3EFy/20)

#### Query 3 : AND statement

Suppose that we are interested only in coffee expenses. Since there is no ```coffee``` column, we can look for rows where the food expense is less than $5(assuming it's coffee).


![SQL Query 3](/images/tasks/query_3.png)SQL [playground](https://www.db-fiddle.com/f/ayVhtzzdadCPS2Lzpr3EFy/21)

#### Query 4 : DISTINCT statement

If we want to list out coffee shops the user has been to, we can use ```DISTINCT``` to remove duplicates and show unique ones.


![SQL Query 4](/images/tasks/query_4.png)SQL [playground](https://www.db-fiddle.com/f/ayVhtzzdadCPS2Lzpr3EFy/22)


#### Query 5 : GROUP BY clause

If we need to aggregate data based on specific columns (for example, CoffeeShop), we can use ```GROUP BY``` to group the data into different buckets and then apply aggregate functions (such as ```SUM```, ```COUNT```) to those buckets.

![SQL Query 5](/images/tasks/query_5.png)SQL [playground](https://www.db-fiddle.com/f/ayVhtzzdadCPS2Lzpr3EFy/23)

#### Query 5 : DATE_TRUNC function

Let's say we want to look at weekly expenses. We can group by ```week``` and sum up the ```amount``` column.

However, this is a bit tricky because the ```week``` is not directly available, only the ```date``` is.

```DATE_TRUNC``` is a function that accepts dates as input and outputs them in the specified format, such as week, month, etc.

``` DATE_TRUNC('week', DATE '2022-05-10') → 2022-05-09```
``` DATE_TRUNC('week', DATE '2022-05-12') → 2022-05-09```


![SQL Query 6](/images/tasks/query_6.png)SQL [playground](https://www.db-fiddle.com/f/ayVhtzzdadCPS2Lzpr3EFy/25)

Please feel free to experiment with SQLs using the playground (link available under the above images).

Learn SQL basics

Getting started with SQL


There are thousands of [dapps](https://www.stateofthedapps.com/platforms/ethereum) running on the Ethereum network. Their backend logic and data are stored and executed in the blockchain.

![Dapps on Ethereum](/images/tasks/dapps_in_ethereum.png)Decentralized applications running on Ethereum network

#### Who owns the data?

No one but everyone can access the data.

#### How does anyone get access to the data?

Every (archive) node in the Ethereum network contains a complete copy of the blockchain data.
All we have to do is download and run the Ethereum client.
The entire blockchain data would be downloaded to the machine.

Although this gives us access to the data, the raw data is not stored in a format suitable for analytical queries. We would have to extract data from the node, transform it, and then load it in a queryable format.

[Blockchain-ETL](https://github.com/blockchain-etl) is a set of open source scripts that allow us to transform and load data into a relational database so that we can query it using SQL.

![Host blockchain data](/images/tasks/blockchain_etl.png)DIY blockchain data hosting

Although the above option is ideal for anyone who wants to host their own blockchain database, it is the hardest to get started since it requires

🥱 Setting up and maintaining an archive node.

😫 Managing the ETL process and the database service.

There are providers who take away the complexity of the above process and allow us to query data directly using SQL or GraphQL.

![Blockchain data providers](/images/tasks/blockchain_data_providers.png)Blockchain data providers


Get to know the data providers

How to Query Blockchain Data

Ethereum is a decentralized protocol that supports smart contract execution. 

What does that mean?

Let’s decode DECENTRALIZATION

![Network Architecture](/images/tasks/centralised_decentralised.png)Network Architecture

A centralized system is owned by a single or group of entities. 
They provide value by facilitating transactions between the participants, unless otherwise it is hard to achieve.
The vast majority of the systems with which we interact are centralized. 

For instance, Facebook enables us to connect with friends and communities all over the world.
While they have played a huge role in democratizing communication, they are centralized and are the sole authority of the network.

![Web2 Centralized Architecture](/images/tasks/web2_animation.png)Web2 Architecture

**Is that a problem?**

Some of the commonly raised issues are:

🔒 Data Privacy : They provide us free services in exchange for rights to our own data. How do we know that it safeguarded from hacks and misuse?

🧠 Censorship and Bias: We have to trust their system to provide us with unbiased information. They can change the rules at any time such as banning groups or individuals, increasing ad fees etc.

🛂 Data Portability : Our data is closed behind their walls, and it is difficult for us to transfer data between platforms. As a result, despite whatever happens, we are stuck with them.

💲 Monetization : While they monetize based on user-generated data, content owners are not rewarded fairly.

These issues are not unique to any one system, but are a common problems in centralized systems.



In a decentralized architecture, there is no single entity that controls the system. 
Instead it is run by a group of independent participants. 
Resources are not owned by anyone entity and every participant has complete access to it.

It promises to solve the problems that we face today with the centralized systems.


**So, is Ethereum a decentralized Facebook or a decentralized bank or ?**

No, Ethereum is a decentralized network and provides the infrastructure to build any decentralized applications (dapps) on top of it.

We could build 

🌐 Social Networks (like Facebook, Twitter, Medium)

🏦 Financial Services (Banks, Exchanges, Lenders)

🎮 Entertainment (Games, Arts)

🏢 Organizations

and so on. 

The opportunities are endless.

Instead of a central server, a decentralized application will run on a blockchain and everyone will have access to its data.

What happens if a social network like Facebook is built on Ethereum?

![Web3 Decentralized Architecture](/images/tasks/facebook_web3_architecture.png)Web3 Architecture

The logic that defines the rules of the system and the interaction between participants is deployed to the Ethereum network instead of a central server.
This is what we call "Smart Contracts". 

Smart contracts are computer programs that are stored and run on the blockchain. By deploying to the network, the code becomes available for anyone to inspect. Since the network executes the code, there is no need for an intermediary to host and execute the code.

The smart contract's data will be stored in a decentralized manner.

- Users and their post ownership will be recorded in the blockchain.
- The actual content (images, videos etc) will be stored in a decentralized storage (such as IPFS/Arweave) since blockchain is not suitable for large files.

Anyone can access the above data to develop competitive insights.

For instance, We cannot query the Facebook database to retrieve all viral posts. However, with such a decentralized system, this is possible.

Sounds cool, Isn't it?

Aside from the example above, are there any real-world applications built on the Ethereum blockchain?

There are 2000+ dapps running on Ethereum network. Check [here](https://www.stateofthedapps.com/platforms/ethereum)

Know more about the decentralized protocol

Learn more about the Decentralized Protocol

![Blockchain SQL Cover](/images/articles/web3-data-sql/blockchain_sql_cover.png)Designed in [Canva](https://www.canva.com)

Despite the fact that Ethereum was launched in 2015, there was no easy way to query blockchain data until a few years ago.
Although Ethereum clients expose APIs for accessing account balances and transaction logs, they lack options for efficiently filtering and aggregating data for analytical purposes.

In this article, we'll take a look at four data providers who are extracting and transforming blockchain data into relational databases so that analysts and researchers can explore it using SQL.


#### Blockchain ETL - Self Hosted

[Blockchain ETL](https://github.com/blockchain-etl) is a set of open source scripts for extracting, transforming, and loading blockchain data into csv or a relational database.
This gives you complete control over exporting blockchain data to your own database.
However, this involves a bit of overhead before you start querying.

If you're just getting started and want to start exploring right away, the dataset is hosted in Google BigQuery.

#### Blockchain ETL - Google BigQuery

Google cloud hosts [blockchain data](https://cloud.google.com/blog/products/data-analytics/introducing-six-new-cryptocurrencies-in-bigquery-public-datasets-and-how-to-analyze-them) as part of its public dataset program. It only supports six blockchain projects including Ethereum.
You can immediately begin writing SQL queries in the BigQuery console. It also provides an estimate of how much data your query will consume.
If you want to get fancy, you can use Colab to load an Ethereum dataset into a notebook and carryout some interesting Ethereum data analysis.

Users may query up to 1 TB of data for free each month. After that, each 1TB of data will cost $5.
Google cloud offers new users $300 in credit, allowing them to query 60 TB of data for free before having to start paying.

#### Dune Analytics

[Dune Analytics](https://dune.com) enables community driven blockchain analytics. It offers a SQL interface similar to BigQuery. 
Furthermore, it allows users to discover other user's queries, fork them, and experiment with them.
It is especially useful when you are just getting started and need some guidance to progress further.
As part of their community, you can learn about any bounties, freelance, and full-time job opportunities.

It is free to query.
If you want to download results or gain API access, you can do so by subscribing to a paid plan.

#### FlipSide Crypto

Similar to Dune, the [Flipside Crypto](https://flipsidecrypto.xyz/) provides community-enabled analytics. 
With a free account, you can download query results to csv and also get API access, which is great if you want to export the data to a jupyter notebook to do some exploratory data analysis.

Flipside's bounties are organized into levels to allow anyone to get started quickly. They also have Godmode, which grants you access to far more resources than usual.

#### FootPrint Analytics

[FootPrint Analytics](https://www.footprint.network/) offers flexibility and caters to the needs of diverse users. In addition to SQL, you can use their drag-and-drop interface to quickly create charts.
Similar to the previous tools, it provides options for finding and replicating community queries and dashboards to help you get started quickly.

#### Token Flow

[Token Flow](https://tokenflow.live/) is a new one that has just launched their beta program.
What we see as promising with Token Flow is the ability to query state data, which other tools do not allow yet.
Similar to BigQuery, storage is free, but computation must be paid for, though free credits are available.

With blockchain containing a wealth of information, it is exciting to follow how the blockchain data analytics ecosystem evolves.
You could begin with any of the tools mentioned above. Begin by exploring the tools and engaging with their community. Find out what works best for you and start your web3 analytics journey.

Five tools to access blockchain data using SQL

![Ethereum Raw Data](/images/articles/eth-raw-data-overview/ethereum_raw_blog_cover.png)Source Content : [Yahoo](https://news.yahoo.com/bored-ape-yacht-club-creators-133825145.html)

How does anyone get this information?

The public blockchain records everything that happens on the Ethereum network. Every participating node contains raw data that can be queried to learn or answer questions about what is going on in the ecosystem.

Let's take a look at the data stores in an Ethereum node: global state, transactions, traces, logs, and mempool.

![Ethereum Raw Data](/images/articles/eth-raw-data-overview/ethereum_data_stores.png)Ethereum Raw Data

#### Global State

Every participating node in Ethereum maintains a global state, which is continuously updated as new blocks are added to the blockchain.

The state contains

* ETH balances of all accounts.
* Smart contract code and data. For an NFT contract, the state could contain tokens and ownership information. For a DAO contract, it could be tokens, members, voting control, or treasury balance.

Since all the information is stored in the state, anyone can query the most recent state to learn the truth.

#### Transactions

Any changes to the global state happen via transaction. Transactions are grouped into batches (generally referred to as "blocks") and then added to the blockchain.

Some of the common transactions types are

* ETH transfer between accounts.
* Deployment of a new smart contract. For example, Nike launches their own NFT.
* Smart contract state changes. For example, a user purchases an NFT token.

#### Traces

Traces capture detailed run time data of the transactions.
During execution, a transaction may fail, call another contract, or transfer ETH.
This activity is not recorded in the blockchain but can be seen in the traces.

Most nodes do not store traces by default because it increases storage costs and has an impact on node performance.
However, it is critical data for understanding what is going on within the transaction that cannot be seen otherwise.

Some common scenarios for tracing are as follows:
- Learn more about transaction failures.
- Internal token transfers. A smart contract may airdrop its token to specific users.
- Internal Contract calls. A smart contract might call another contract to determine whether a user is eligible to claim tokens.
- Self destruct. A smart contract removes itself from blockchain and transfers balance to a specific account.


#### Logs

Logs are used to store data that is not required by the contract but must be accessed by other applications (UI notifications, analytics, oracles etc).
Some uses for logs are 

- Display notifications to the user about the transaction status.
- an NFT marketplace app that wants to display token ownership history can do so by searching logs for all transfer events involving the specific token.


#### Mempool

Mempool contains transactions that are waiting to be added to the blockchain. 
Since each node can be configured differently, Mempool data may not be the same across all nodes.
They are used for a variety of purposes, including 

- Analysing pending transactions to adjust gas prices for new transactions.
- Front running a trade.

### Conclusion

All of the blockchain data is public and can be accessed by anyone. Using the data points listed above, one can surface insights about what is going on in the blockchain network.

However, it is easier said than done due to 

- Overhead in setting up and maintaining an Ethereum node.
- Data retrieval and transformation challenges.

Many data providers (TheGraph, Dune Analytics, and Flipside Crypto) are attempting to make blockchain data more accessible.

List Ethereum Transactions using Dune Analytics

Getting started with SQL

How to Query Blockchain Data

Learn more about the Decentralized Protocol

Five tools to access blockchain data using SQL

Ethereum Raw Data - An Overview