Project overview


✅ **Prerequisites**

- Basic knowledge about Ethereum.
- How to run Dune Queries.

If you are not familiar with the above, We've got you covered. 

Please take a quick detour to our introductory project: [Build Ethereum Dashboard](https://www.twigblock.com/projects/eth-intro-dune/t/eit-overview)

⏲️ **30 min**

📖 In this **Guided quest**, you will learn about

- Consensus 🤝
- Staking 💵
- Validators 🛡️
- Merge Impact 🌱

by solving 9 SQL challenges.

By the end of this project, you should be able to create your first Proof of Stake dashboard, such as the one below.

![Attestation](/images/tasks/eth_merge_dashboard.png)Source: [Dune](https://dune.com/kikura/ethereum-proof-of-stake)

The SQL challenges can be solved by forking the query and fixing the todo items as shown below.

![How to solve](/images/tasks/how_to_solve.gif)

Let's get started 🎬

&nbsp;


What you'll be solving

Analyze Ethereum Staking and Merge Data

Solve quests

Every Ethereum node stores the entire state of accounts and smart contract.

When you query any node about the state, for example, 

- What is the balance of an ETH account?
- Who owns this NFT? 

Every node should provide the same response.

**How to achieve that when the nodes are distributed and each has its own copy of the state?**

Any state changes are trigged by transactions. To achieve the same shared global state, all nodes must agree to process the same transactions in same order. This is called CONSENSUS.

This is achieved by allowing one of the nodes to propose the next block (of transactions and their order). Every other node verifies and process the transactions in the proposed block to update their state. The block proposer gets a reward for doing so.

Because there is a reward, it is natural for every node to constantly propose blocks.

To avoid every node from flooding the network with their own proposals and to ensure every node agrees on the same block, Ethereum used a mechanism called PROOF OF WORK (PoW).

In PoW, each node processes the pending transactions, organizes them into a block, and then follows the process outlined below to propose their block.

- Calculate the hash of their block and a random number (aka nonce). 
- If the resulting hash is less than a specific value (aka target), they can propose their block.
- If not, they change the nonce and repeat the process described above until they meet the condition or someone claims to have solved it.

![Mining Process](/images/tasks/ethereum_pow_mining.png)Node 1 solved the mining puzzle.

If some other node claims to solve it, phew! 😤 their effort has gone to waste. They verify and add the newly received block to the blockchain and start the process all over again to find the next block. This process of solving puzzles to propose blocks is called MINING.

Since this is a trial-and-error approach, the node that can compute the hashes quickly is more likely to solve the puzzle first. As a result, miners invest in powerful hardware that consumes a lot of electricity.

On average, it takes ~10-15 seconds for some node in the network to solve the puzzle and propose a block.
When more miners join the network, it results in more hash power, and the puzzle can be solved much faster.

The Ethereum network adjusts the difficulty after every block so that the expected time to find the next block is ~10-15 seconds. When more nodes join the network, the difficulty is increased. The difficulty decreases as nodes leave the network.

The ```difficulty``` is stored in the block header. We can understand mining activity during a specific time period by observing the block difficulty.

**🕵️ When did Ethereum reach its all time high in mining difficulty?**

```
SELECT
 DATE_TRUNC('day', `time`) as day,
 --1. Take average of difficulty column
FROM
 --2. Read from blocks table
GROUP BY
 day
--3. Plot the data in a bar chat to visually observe the mining difficulty
```
[Click here](https://dune.com/queries/1341682) to solve the quest.

Proof of Work

💡 Choose your option below 💡

🧗 When did Ethereum reach its all time high in mining difficulty?

Instead of requiring nodes to spend energy to do block proposals, the Proof of Stake mechanism required nodes to deposit ETH and take turns to propose blocks based on the proportion of the staked amount.

The transition from PoW to PoS happened in two phases

- Phase 0 - Beacon chain launch
- Phase 1 - Merge beacon chain to Ethereum network (aka mainnet)

Ethereum deployed a smart contract ```0x00000000219ab540356cBB839Cbe05303d7705Fa``` to the mainnet and asked stakers to deposit ETH to participate in the PoS consensus. 

The beacon chain was activated when the staking deposit in mainnet reached a certain amount.

When it launched, it was not processing any Ethereum transactions. Instead, it was just monitoring the mainnet for any staking deposits, activating validators and creating beacon blocks with just deposit data. In essence, the beacon chain was reaching consensus on the validator state and deposit information.

![Beacon chain](/images/tasks/eth_beacon_chain.png)Beacon chain and Ethereum mainnet running in parallel

**🕵️ When did the beacon chain go live?**


```
WITH eth_deposit_status AS (
 SELECT block_time,
 SUM(value/1e18) OVER (ORDER by block_time) AS total_eth_deposited
 FROM --1. Read from traces table
 WHERE `to` = LOWER('depositaddress') --2. Update the deposit contract address
 AND success = true
 AND value > 0
)
SELECT DATE(MIN(block_time)) AS genesis_trigger_date,
 DATE(MIN(block_time + INTERVAL '7 DAY')) AS beacon_chain_launch_date
FROM eth_deposit_status
--2. Add a where condition to ensure the total_eth_deposited >= 524288
```
[Click here](https://dune.com/queries/1358000) to solve the quest.

Proof of Stake

🧗 When did the beacon chain go live?


**🕵️ Find out the total ETH deposited so far**

```
SELECT --1.sum up the value column and divide by 1e18 to report in ETH
FROM --2. Read from traces table
WHERE --3. Add condition to filter for deposit contract address
--4. Include only successful transactions

```
[Click here](https://dune.com/queries/1364599) to solve the quest.



Total ETH deposited

🧗 Find out the total ETH deposited so far


#### How do we know the total validators?

When a deposit is made to the ETH2 staking deposit address, the transaction includes:

- Validator's public key
- Validator's withdrawal key 
- Amount deposited

Once the transaction is successfully processed by the EVM, it emits an event that is captured in logs. The beacon chain monitors the logs for deposit events and registers the validator.

![Validator activation](/images/tasks/eth_validator_activation.png)Validator activation lifecycle

By querying the logs for unique validator public keys, we should be able to get the total number of validators signed up so far.

*Note: All of the registered validators may not be active now. The current status is available only in beacon chain*

#### 🕵️ Find the total number of validators registered so far.

```
SELECT
  --1.SUBSTRING(data, 387, 96) gives validator public address. Use COUNT and DISTINCT to retrieve unique validator count
FROM
  --2. Read from ethereum.logs
WHERE
  topic1 = '0x649bbc62d0e31342afea4e5cd82d4049e7e1ee912fc0889aa790803be39038c5' --keccak hash of deposit event
  AND contract_address = LOWER('0x00000000219ab540356cBB839Cbe05303d7705Fa') --ethereum deposit address
```
[Click here](https://dune.com/queries/1364662) to solve the quest.



Number of validators

🧗 Find the total number of validators registered so far



The amount required to act as a validator is 32 ETH. If you have 32 ETH, you can directly deposit into the deposit contract and run or delegate your validator node.

If you don't have 32 ETH, you could still participate via

- Staking pools
- Centralized exchanges

![Staking options](/images/tasks/eth_staking_options.png)Staking options

#### Find out the Unique depositors

```
SELECT --1. Count distinct from address that made the deposit
FROM --2. Read from traces table
WHERE  `to` = LOWER('0x00000000219ab540356cBB839Cbe05303d7705Fa') 
--3. Consider only successful transactions.
```
[Click here](https://dune.com/queries/1364773) to solve the quest.


Unique depositors

🧗 How many unique depositors are participating in ETH staking?

When the exchanges or the staking service runs the majority of the validators, it can make the network more prone to failure due to malicious attacks, outages, client bugs etc. 

Let's try to find out the network penetration of these staking entities.

We can group the deposits by depositor address and understand the depositor distribution. We could refer to the etherscan or Dune ```labels``` table to identify the entity name. Keep in mind that not all addresses may have labels. 

Furthermore, some exchanges employ a wallet hopping strategy, in which they deposit ETH in a new wallet, send the deposit from the new wallet, and return the excess to a known wallet. In this case, we have to write rules to track and identify the entity. 

To keep it simple, let us just look at the depositor address and their etherscan label.

**🕵️ Which entity has the highest stake percentage?**

```
WITH depositors AS (
 SELECT `from` AS depositor,
 SUM(value) / 1e18 AS eth_deposited
 ...
 ),
 --1. Creat a deposit_summary CTE that sums up the total ETH deposit from the above CTE
SELECT 
 d.depositor,
 '<a href=https://etherscan.io/address/' || d.depositor || ' target=_blank>EtherScan</a>' as etherscan_link,
 eth_deposited,
 --3. Divide eth_deposited by total_eth_deposited to compute the staking percentage
FROM
 depositors d
 --2. join with deposit_summary CTE
ORDER BY eth_deposited DESC
--3. Once query is executed, Inspect the etherscan link of the first address to identify the entity
```
[Click here](https://dune.com/queries/1358540) to solve the quest.

Network penetration

🧗 Which entity has the highest stake percentage?

Let's look at how the beacon chain achieves consensus.

Consensus is all about agreeing on the order of blocks(transactions).

In PoS, time is divided into 12 seconds(aka slot) and 6.4 minutes(aka epoch). 

A random validator is chosen by a process called RANDAO to propose the block at every slot.

Once the validator proposes the block, other validators can verify and add the proposed block to their view of blockchain.

**What happens when some nodes disagree?**

Let's say due to network delay, the proposer at slot#4(see below) did not receive the previous block. They assume that no one proposed the previous block (which can happen if the chosen validator is offline) and built theirs on top of the last observed block. This results in a fork.

![Fork Choice](/images/tasks/eth_fork_choice.png)Fork choice

**When the next proposer has to build their block, Should they choose block at slot#3 or slot#4 as their parent?**

This can be solved by asking all other nodes about their view of the blockchain. If majority of them said they have seen block at slot#3, then the current proposer could consider that as a valid(aka canonical) chain and can build on top of block at slot #3.

Instead of the proposer asking every node about their view, this is achieved by asking the nodes to vote for the head block at every slot. By aggregating the votes for every fork path, the validators can agree on the chain that has the majority votes.

![Fork Choice](/images/tasks/eth_fork_choice_ghost.png)Fork choice - Attestation

Since there are so many validators, it would overwhelm the network if everyone has to vote on every slot. To better handle this, validators are grouped into 32 committees and will be randomly assigned to vote for one slot every epoch. 

Further, the votes are aggregated at subnets and then forwarded to the block proposer. 

The block proposer is responsible for including the attestations(votes) as part of their block.

In addition to the head block votes(aka LMD GHOST vote), the validators also vote on checkpoints(aka CASPER FFG vote):

- source checkpoint: first block of last epoch
- target checkpoint: first block of current epoch

![Attestation](/images/tasks/eth_casper_ghost_vote.png)Validator Attestation

Once 2/3 validators vote on a checkpoint twice i.e once as source and once as target, it is finalized. The blocks are confirmed and cannot be reversed. This is called finality.

![Finality](/images/tasks/eth_finality.png)finality

*Note: The blocks could still be reversed after finality. Let's say a new path has been voted by 2/3rd of validators. This can happen only when 1/3 of validators voted for both paths. As part of slashing, they will lose their entire stake. With currently ~14M ETH staked, the slashed validators would lose approximately ~4.7M ETH(=6B USD as of Oct'22). We could say the finality is guaranteed with $6B USD in stake*

In essence, the validator has to
- propose blocks during their turn
- attest(vote) on head block once per epoch
- attest(vote) on source and target checkpoints once per epoch

The validators will be rewarded for timely block proposal and attestations. They will be penalized for missing their duties.

**How do we get the validator data?**

The validator state is stored in the beacon chain. Since beacon chain is not accessible in Dune yet, let us investigate from block explorers.

**🕵️ Number of block proposals by validator 10467**

[Click here](https://beaconcha.in/validator/104767) to solve the quest.

Validator Roles

🧗 Find out number of block proposals by a validator

The inherent assumption of blockchain is that no node can be trusted. Hence, there has to be a mechanism for monitoring malicious behaviors and penalize them for it.

As per the protocol rules, Validators are NOT supposed to:

a) cast a contradictory vote. when a validator votes twice for two blocks in the same slot.

![Mining Process](/images/tasks/eth_attestation_violation.png)Attestation Violation

b) propose two different blocks at the same slot. 

![Mining Process](/images/tasks/eth_proposer_violation.png)Proposer violation

Since every information is recorded in the beacon chain, any other node can go through the history to identify and report such violations. These violations result in validator slashings, which means the validator is penalized and kicked off the network. The block proposer that included the slashings will be rewarded for reporting it.

**How do we get the validator data?**

The validator state is stored in the beacon chain. Since beacon chain is not accessible in Dune yet, let us investigate from block explorers.

**🕵️ Number of slashing occurrences in the beacon chain**

[Click here](https://beaconcha.in/validators/slashings) to solve the quest.

Validator Slashings

🧗 Find out the slashing occurrences


The beacon chain was activated in December 2020. It didn't process any Ethereum transactions until the merge. 
It was primarily run to allow stakers to join the network, propose, and attest beacon blocks.

The Ethereum mainnet used PoW consensus until the merge.

The second phase of PoS is to merge the mainnet with the beacon chain.


![Beacon chain](/images/tasks/eth_merge_1.png)The Merge

![Beacon chain](/images/tasks/eth_merge_2.png)PoW to PoS consensus

**🕵️ When did Beacon chain merged to the mainnet?**

In PoW, each block has a difficulty, as well as a cumulative difficulty from the genesis block.

The merge was configured to happen when the cumulative difficulty reached a certain threshold. The execution layer then started communicating with the beacon chain instead of the PoW process.


```
SELECT 
    --1. select the first block based on time
FROM ethereum.blocks
--2. Select only the blocks with total_difficulty greater than or equal to 58750000000000000000000

```
[Click here](https://dune.com/queries/1385917) to solve the quest.



Merge to mainnet

🧗 When did Beacon chain merged to the mainnet?


The Merge promised to bring 
- reduction in energy consumption.
- reduction in ETH issuance.

Energy consumption is affected by a variety of factors, including device hardware, number of nodes, and so on. It becomes difficult to predict without reliable information. Nonetheless, a substantial amount of research has been conducted on this subject.

We will use the [Digiconomist energy index](https://digiconomist.net/ethereum-energy-consumption) for this task. Digiconomist has released an API that provides energy consumption metrics at a more granular level (per gas unit).

We've prepared the data using this [snippet](https://gist.github.com/kikura3/b3847100817764fadc63e5c5cae8275d).


#### Can you find out change in energy consumption?


```
WITH
  energy_metrics(dt, gasunit_wh, gasunit_gco2) AS (
    SELECT
      *
    FROM
      (
        values
          ('2022-08-01', 2.35668, 1.31446),
          ...
      )
  )
SELECT
  DATE_TRUNC('day', `time`) AS day,
  --1. Multiply gas_used with gas_unit_wh, divide by 1000 to convert to kwH
  --2. If value not available, use 0.0003 as median estimate after merge i.e. COALESCE(gasunit_wh,0.0003)
  SUM(gas_used * COALESCE(gasunit_wh,0.0003)/1000) AS daily_energy_kwh
FROM
  ethereum.blocks eb
  LEFT JOIN energy_metrics em ON DATE(eb.time) = em.dt
WHERE
  `time` >= DATE_TRUNC('week', '2022-08-01') --The data is fetched only from August
GROUP BY day
ORDER BY day
--3. Use reference dates as 09-14 , 09-16 to compute the change in energy consumption

```
[Click here](https://dune.com/queries/1358676) to solve the quest.



Merge Impact - Energy consumption

🧗 Find out the energy reduction


New ETH is created and issued continuously as rewards to incentivize miners(validators) to secure the network.

Since validators use staked ETH to secure the network instead of mining, they no longer need to run expensive hardware. Hence, the rewards in PoS are lower than in PoW.

![ETH issuance](/images/tasks/eth_net_issuance.png)ETH issuance

```txt
Net ETH issuance = PoW Rewards (before merge) + Validator Rewards - ETH burnt
```

The validator rewards are stored in Beacon state. Since beacon state is not available in Dune analytics, we can approximate the daily PoS ETH issuance by computing the upper bound using the below [formula](https://eth2book.info/altair/part2/incentives/issuance):

```txt 
Daily PoS issuance(upper bound) = 
  no_of_epocs_per_day *  
  ( effective_balance * base_factor * num_validators  / 
        sqrt(effective_balance * base_factor * num_validators))

where no_of_epocs_per_day = 225,
      effective_balance = 32ETH,
      base_factor = 64
```




**🕵️ Can you find the drop in ETH issuance per day?**



```
WITH block_rewards AS ( 
    ...
    --1. Add a condition (type=reward) to retrieve the paid rewards. 
    ...),
fees_burnt AS ( 
    ...
    --2. compute fees_burnt by multiplying gas_used and base_fee_per_gas. Divide by 1e18 to convert to ETH
    ...),
...
 eth_issuance_pos AS (
   ...
 )
SELECT 
    ev.day, 
    estimated_maxeth_issued_pos AS estimated_maxeth_issued_pos, 
    COALESCE(block_reward,0) AS block_reward,
    COALESCE(fees_burnt,0) as eth_burnt,
    --3. add block_reward, estimated_maxeth_issued_pos and subtract fees_burnt to compute estimated net issurance
FROM eth_issuance_pos ev 
LEFT JOIN fees_burnt fb 
    ON ev.day = fb.day 
LEFT join block_rewards br 
    ON ev.day = br.day
--4. Create a bar chart on the columns to visually observe the drop in ETH issuance

```
[Click here](https://dune.com/queries/1358756) to solve the quest.



Merge Impact - ETH issuance

🧗 Can you find the drop in ETH issuance per day?

Build your own narrative

Yeah! That's a lot to take in. Hopefully, you are now familiar with these terms and know how to extract them using SQL.

![Ethereum Gas Terms](/images/tasks/eth_consensus_terms.png)Ethereum Consensus Topics

Explore the reference [dashboard](https://dune.com/kikura/ethereum-proof-of-stake).

Here are some ideas to expand further
- To understand network penetration, add labels (Lido, Binance, etc.) and categories (CEX, staking pool) to the depositor address.
- Compute the trend or growth of staking entities and validators over time.
- Plot the block time, block count, empty blocks, gas price before and after merge to understand the impact.

#### What comes next?

- We have looked at Ethereum Consensus.
- Next, let us explore the foundational topics on Smart Contracts, Events, Token Standards, Layer 2 solutions.
- 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.

&nbsp;

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