Delving into Ethereum and Layer 2 Solutions: An In-Depth Exploration with Dune Analytics

eekeyguy
5 min readJan 12, 2024

--

Objectives: Provide users with a comprehensive understanding of Ethereum and its ecosystem by:

1/ Tracking inflows/outflows of Layer 2 (L2).

2/ Monitoring the growth of L2s in terms of activities within the ecosystem, such as Daily Active Users (DAU), DEX volume, ERC-721 transfers, on-chain profit, etc.

Tracking Inflows/Outflows of L2 :

Inflows and outflows are monitored by tracking the value of tokens sent to and from the L2 chain. This involves two types: Canonically Bridged Value (CBV) and Externally Bridged Value (EBV).

Note : Natively Minted Tokens are not tracked.

The Total Value Locked (TVL) in the dashboard is the sum of CBV and EBV, differentiating it from platforms like DefiLlama. DefiLlama focuses on assets actively engaged in dApps, while our TVL includes tokens bridged to L2, whether or not actively used in applications.

How TVL Metrics are Calculated:

TVL = Canonically Bridged Value (CBV) + Externally Bridged Value (EBV)

  • CBV Calculation: tokens_locked_on_L1 * price_on_L1
  • EBV Calculation: total_supply_on_L2 * price_on_L2

Challenges Faced and Solutions During Calculations:

Challenge 1: Tracking Bridged Tokens and Identifying Externally Bridged (EB) Tokens

Solution: To address the challenge of tracking bridged tokens and determining if we need to monitor EB tokens for a given chain, we implemented the following solutions:

Official Bridging Addresses: L2beat provides clear definitions of official bridging addresses for each Layer 2 (L2). Utilizing these addresses, we calculated the Total Value Locked (TVL).

Canonical Bridging (CB) Tracking: The erc20_ethereum.evt_Transfer table is instrumental in tracking all tokens bridged canonically.

Externally Bridged Token Tracking: For EB tokens, we monitor tokens minted from the null address. For instance:

L2beat Token Filtering: We exclusively track tokens specified on L2beat to enhance accuracy. This involves applying a filter to select tokens only from L2beat, such as symbol in (‘ETH’,’USDT’,’USDC’,’WBTC’,’wstETH’,’DAI’,’LINK’, and more).

Challenge 2 (Main Challenge): Lack of USD Prices for Tokens

Solution: The absence of USD prices for numerous tokens, particularly in the case of chains like Arbitrum with over 100 canonically bridged tokens, posed a significant challenge. To overcome this, we devised the following solution:

We extracted the latest DEX trades for all tracked tokens and derived USD prices from these trades. This approach ensured the inclusion of accurate token values in our calculations.

Separate Data Sources for CB and EB Tokens: For CB tokens, we considered the latest trades on Ethereum, while for EB tokens, we utilized the latest trades on the respective Layer 2 networks.

Challenge 3: Manual Efforts in Dealing with Bridged Tokens

Solution: Manual efforts were invested in addressing challenges related to the naming discrepancies of bridged tokens. Our solution involved:

L2beat Referencing: We cross-referenced all tracked tokens with L2beat, manually verifying token names to avoid any mismatches. For example:

These measures ensured accurate and efficient tracking of bridged tokens within the Ethereum ecosystem.

Tracking the Growth and Activities of Layer 2 (L2) Networks:

Daily Active Addresses on Ethereum and L2:

Insight: Provides an overview of how L2 networks contribute to scaling the Ethereum ecosystem, evidenced by a threefold increase in total distinct users.

Data Source: Utilizes the evms.transactions table.

Transactions Count:

Insight: Reveals the evolving nature of L2 networks over time and identifies specific chains gaining traction.

Data Source: Relies on the evms.transactions table.

Average Gas Fees:

Importance: Critical metric considering Ethereum’s sustainability challenges. Users prefer low gas fee solutions for day-to-day activities.

Methodology: Applies the average function to gas fees. Challenges arise due to differing fee calculation formulas for each chain.

Solution: Selects one transaction from each chain and apply chain-formulas for accurate gas fee computation and compare values with etherscan, arbiscan etc……

Since there is huge difference in gas values hence 2 y-axis were used to show proper representation of all chains.

ETH Burned in the Past 30 Days:

Insight: Illustrates how L2 networks contribute to Ethereum’s deflationary mechanism by tracking ETH burned during L2 settlement on Ethereum.

Methodology: Tracks transactions of L2 sequencers, proposers, and challengers on Ethereum, calculating ETH burned using a specific formula.

Challenge: Identifying sequencer/proposer transactions.

Solution: Leverages L2beat’s provided addresses of sequencers and proposers for accurate tracking.

Weekly On-chain Profit:

Significance: A key metric determining the self-sustainability of a chain.

Calculation: Profit = Revenue — Expenses, where Revenue is the sum of all gas paid by users, and Expenses are transactions by L2 sequencers/proposers on Ethereum for settlement activities.

This calculation methodology differs from any Dune query as the rollup_economics table is not utilized. Instead, we have opted for a fundamental approach, drawing insights from official documentation and L2beat.

Weekly Smart contracts deployed :

Insight: provides insights into the development and activity level within that blockchain ecosystem. A rising number of deployed smart contracts typically indicates a growing developer community and increased adoption of the blockchain for various decentralized applications (DApps) and projects.

Methodology: Tracks contracts created using evms.creation_traces.

DEX Volume comparisons :

Methodology: Use dex.trades.

Additional Tracked Metrics:

  • Most used smart contracts
  • Daily ERC721 contracts.

--

--