Project Overview


✅ No Prerequisites

⏲️ 30 min

📖 In this Guided project, you will

- Learn about the Ethereum protocol and data providers.
- Understand basic SQL queries (such as SELECT, GROUPBY).
- Write queries to extract information from the Ethereum blockchain.
- Build an introductory dashboard that tracks smart contracts, wallets, and transactions using Dune Analytics.

At the end of this project, you will have built a dashboard similar to this one.

![Dune Analytics Dashboard Image](/images/tasks/eit_dashboard.png)Source: [Dune](https://dune.com/kikura/ethereum-dashboard-beginners) - Credits: @hildobby's [work](https://dune.com/hildobby/Ethereum-Overview)


Let's get the ball rolling 🎬


What you'll be building

Build Ethereum Metrics Dashboard

Ethereum Basics

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?

We'll leave it up to you to figure it out 🕵️‍♀️

Know more about the decentralized protocol

Can you find the total number of dapps on the Ethereum network?

👁️ Let's explore! 

Learn more about the Decentralized Protocol


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

Which of the following tools allows us to query blockchain data using SQL?

📘 Let's do a quick recap!

How to Query Blockchain Data

Create Metrics Charts


Before we proceed further, let's go over SQL basics. If you are familiar, you can skip ahead to the next section.

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)

That is all the SQL knowledge you will require to complete this project. 

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


Learn SQL basics

Which one of the following allows you to find the total amount spent at every merchant? 

💡 Quiz time

Getting started with SQL

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

Copy the Dune query URL and update it here.

💾 Lets save your progress

List Ethereum Transactions using Dune Analytics

Let's build a counter to display the total number of smart contracts created so far.

Smart contracts are created in two ways.

- An external account sending a transaction to create a smart contract. We've seen this in the last section.

- A Smart contract sending an instruction to create another smart contract. Yes. When a transaction is executed, it can create or interact with other smart contracts.

![Smart Contracts Creation Scenarios](/images/tasks/internal_transaction_scroller_2.png)Smart Contracts Creation Scenarios

In the second workflow, the user triggered a call to an existing contract. In response to that, the smart contract ```#0xb000``` created a new smart contract ```#0xb999```.

Not only can a smart contract create another smart contract, but it can also call an existing smart contract, as well as send ETH to other accounts.

These are known as internal transactions, and they are NOT recorded as transactions in the blocks.

If they are not in ```blocks```, where do we get that information?

EVM records the output of each step of the transaction execution in traces.

We will be able to list all smart contracts created thus far, including those created internally, by scanning the traces.

![Dune Analytics ETL](/images/tasks/dune_traces.png)Dune Data Load

We know where to look now.

Let's run a query against the ```traces``` table to list only contract creation data.
We can add a condition to retrieve only the required rows because traces contain other run time data as well.

```SQL
/* Query to retrieve total number of smart contracts created so far */
SELECT
 --Divide by 1,000,000 (or 1e6) to report in millions
 COUNT(*) / 1e6 AS contracts_count 
FROM
 ethereum.traces
WHERE
 `type` = 'create';
```

👨🏽‍💻 Can you create a counter chart for the above query, as you did in the previous section? 

👉🏽 Please remember to add the chart to the current dashboard.

*Feeling stuck?Check out this [Smart Contracts Chart](https://dune.com/queries/1024314/1767745)*

Report Total Smart Contracts

How to Query Total Smart Contracts Deployed in Ethereum

Let's make a chart for the user accounts that have interacted with the Ethereum network at least once.

**Where are the user accounts stored?**

EVM maintains the user accounts and their balances in the account state. However, Dune Analytics does not yet support the state. 

**So, how do we get it?**

We know that any Ethereum global state changes are triggered by a transaction from a user account (generally referred to as "Externally Owned Accounts").

Externally Owned Accounts(EOA) are defined by:

- a private key known only to the user.
- a public key that is available to anyone who wants.

Whenever a transaction is created, the wallet address (derived from the public key) is included in it.

![Wallet Address in Transaction](/images/tasks/wallet_transaction.png)Wallet Address in Transaction

We can query the ```from``` column in the ```transactions``` table to retrieve all of the user accounts that have interacted with the Ethereum network.
Since the same account may have interacted multiple times, let's use ```DISTINCT``` to find the unique ones.

```
/* Query to retrieve total number of active wallets */
SELECT
 COUNT(DISTINCT `from`)/1e6 AS active_user_accounts
FROM
 ethereum.transactions;
```

There could have been other external accounts that received ETH or other assets but never initiated a transaction. We will not be able to find those in the ```from``` column. 
For the scope of this task, we are only considering accounts that have initiated a transaction at least once.

👨🏽‍💻 Can you create a counter chart for the above query, as you did in the previous section? 

👉🏽 Please remember to add the chart to the current dashboard.

*Feeling stuck? Check out this [Active Wallets Chart](https://dune.com/queries/1030369/1783007)*

Show Active Wallets

List Total Active Ethereum wallets

Let's align the charts before we proceed further.

![Dune dashboard](/images/tasks/align_dune_dashboard.png)Draft Dashboard

The figures above represent the total count since Ethereum’s inception. It does not state the current trend.

- Is there any change in smart contracts deployment?
- What is the impact of the recent market conditions on user activity?

To see the trend, we can group the data by month.

Recall the syntax for ```GROUP BY``` and ```DATE_TRUNC``` syntax from the previous sql section.

We will use ```block_time``` to get the timestamp of the transactions and group them by month to understand the monthly trend.

```
/* Query to retrieve month-wise transaction volume */
SELECT
 DATE_TRUNC('month', block_time) AS month,
 COUNT(*) AS transactions_count
FROM
 ethereum.transactions
WHERE
 //Ignore current month
 block_time < date_trunc('month', NOW())
GROUP BY
 month;
```

👨🏽‍💻 Copy the above query and run it in a new query window.
Let's make a bar chart to represent monthly volume and add it to the existing dashboard.

![Bar Chart Visualization](/images/tasks/dune_barchart_scroller_3.png)Bar Chart Visualization


👨🏽‍💻 Let's repeat the process for creating monthly charts for Smart Contracts and Active Wallets.

👉🏽 Please remember to add the charts to the current dashboard.

*Feeling Stuck? Check the charts for [Smart Contracts](https://dune.com/queries/1034180) and [Active Wallets](https://dune.com/queries/1034273).*

We hope your dashboard now looks like this or even better than this.

![Dune Analytics Dashboard Image](/images/tasks/eit_dashboard.png)Source: [Dune](https://dune.com/kikura/ethereum-dashboard-beginners)

Let's take a moment to examine the dashboard and make a list of any observations or questions we have.

1. Why is there such a large difference in the number of smart contracts (51M+) and dapps (2000+)?
2. Why has the deployment of smart contracts deteriorated sharply since its peak in 2021?
3. Which smart contract receives the most transactions?
4. From June 22 to July 22, there is a significant increase in active users. Is it due to the upcoming merge?

&nbsp;

We can add our questions and observations using text widgets in Dune dashboard. 

Feel free to explore the dashboard and record your observations.

Report monthly changes

Copy the Dune dashboard URL and update it here.

Publish your dashboard

Yay! We have completed the first step in our blockchain analytics journey.

Coming soon! NFT for on-chain credentials. 


![project completion NFT](/images/tasks/sample_nft.png)Sample project completion NFT.

What comes next?

- We have looked at Ethereum data stores (Transactions, Traces, Logs and State). Everything that happens on the Ethereum network is recorded in one of the above stores.
- Next, let us explore Events, Gas Prices and Token Standards.
- Following that, we will delve deeply into sectors (such as DeFi, NFT etc).
- Finally, we will pick a specific protocol (Uniswap, OpenSea, etc ) and conduct extensive research on it.
- And then, You will be ready to start working on web3 data analytics. 🎉

&nbsp;

We are currently in beta. Please DM us on [twitter](https://twitter.com/Kirubakumaresh) for any issues or questions.