Network Stats

This use case demonstrates the capability of ZettaBlock's full-stack Web3 data platform in enabling developers to analyze and visualize key statistics from Layer 1 and Layer 2 blockchain networks.

📘
Example: You can find an analytical dashboard with zkSync metrics here. To obtain a similar analysis for a different chain, simply change the underlying table in the SQL code provided in this use case.

Key Metrics for L1/L2 Networks

Some of the most common metrics to look at when investigating the health and general overview of a chain:

Transaction Volume: Total and average number of transactions over selected timeframes (daily, weekly, monthly).
Gas Fees: Average gas fees over time, highlighting periods of high network congestion.
Active Addresses: The number of unique addresses interacting with the network indicates network utilization.
Smart Contract Interactions: Frequency and type of smart contract executions, signifying the level of network engagement.
Throughput and Scalability: Transactions per second (TPS), block size, and other network capacity metrics.
Validator and Node Statistics: (For Proof of Stake networks) Number of validators, staking amounts, and node performance metrics.
Cross-chain Activity: (For L2 networks) Volume and frequency of cross-chain transactions between L1 and L2.
Protocol-specific Metrics: Custom metrics relevant to specific L1/L2 networks, such as staking volumes or DeFi activity.

Below are some SQL examples and visualizations you can use for your own L1/L2 analytical dashboard.

Core Tabular Data

SELECT
  COUNT(
    DISTINCT CASE
      WHEN STATUS = 1 THEN t.hash
    END
  ) AS number_transactions,
  COUNT(
    DISTINCT CASE
      WHEN STATUS = 0 THEN t.hash
    END
  ) AS number_failed_transactions,
  COUNT(DISTINCT t.from_address) AS unique_users,
  ROUND(SUM((gas_used * gas_price) / POW(10, 18)), 8) AS txn_fees_eth,
  ROUND(
    SUM((gas_used * gas_price) / POW(10, 18)) / COUNT(
      DISTINCT CASE
        WHEN STATUS = 1 THEN t.hash
      END
    ),
    8
  ) AS fees_eth_per_txn,
  AVG(gas_price) / POW(10, 9) AS average_gas_price
FROM
  zksync_mainnet.transactions AS t

Core Metrics - Historical Evolution

SELECT
  *,
  txn_fees_usd / number_transactions AS avg_fee_per_txn_usd
FROM
  (
    SELECT
      core.*,
      number_transactions + number_failed_transactions AS all_transactions,
      number_transactions / 86400.0 AS successful_tps,
      number_failed_transactions / 86400.0 AS failed_tps,
      txn_fees * price_usd AS txn_fees_usd,
      CAST(number_transactions AS DOUBLE) / CAST(unique_active_users AS DOUBLE) AS txns_per_user,
      prices.price_usd,
      users.unique_users,
      users.cum_new_users -- tvl.tvl_usd
    FROM
      (
        SELECT
          DATE(t.block_time) AS date_time,
          COUNT(
            DISTINCT CASE
              WHEN STATUS = 1 THEN t.hash
            END
          ) AS number_transactions,
          COUNT(
            DISTINCT CASE
              WHEN STATUS = 0 THEN t.hash
            END
          ) AS number_failed_transactions,
          CAST(COUNT(DISTINCT t.hash) AS DOUBLE) / 3600.0 AS transactions_per_second,
          COUNT(DISTINCT t.from_address) AS unique_active_users,
          SUM((gas_used * gas_price) / POW(10, 18)) AS txn_fees,
          AVG(base_fee_per_gas) / POW(10, 9) AS average_base_fee_per_gas,
          AVG(gas_price) / POW(10, 9) AS average_gas_price
        FROM
          (
            SELECT
              tx.*,
              b.base_fee_per_gas
            FROM
              zksync_mainnet.transactions tx
              JOIN zksync_mainnet.blocks b ON tx.block_number = b.number
              AND tx.block_date = b.block_date
          ) AS t
        GROUP BY
          1
      ) AS core
      LEFT JOIN (
        SELECT
          DATE(p.minute) AS date_time,
          AVG(price) AS price_usd
        FROM
          prices.usd AS p
        WHERE
          1 = 1
          AND data_creation_date >= DATE('2023-03-24')
          AND LOWER(symbol) = 'eth'
        GROUP BY
          1
      ) AS prices ON core.date_time = prices.date_time
      LEFT JOIN (
        SELECT
          *,
          SUM(unique_users) OVER (
            ORDER BY
              date_time
          ) AS cum_new_users
        FROM
          (
            SELECT
              DATE(block_timestamp) AS date_time,
              COUNT(from_address) AS unique_users
            FROM
              (
                SELECT
                  DISTINCT from_address AS from_address,
                  MIN(t.block_time) AS block_timestamp
                FROM
                  zksync_mainnet.transactions AS t
                GROUP BY
                  1
              )
            GROUP BY
              1
          )
      ) AS users ON core.date_time = users.date_time
    WHERE
      core.date_time >= DATE('2023-03-24')
  )
ORDER BY
  date_time ASC -- WHERE data_creation_date -- Launch 24/03, time 1pm UTC

DeFi Activity

WITH
  v2_pairs AS (
    SELECT
      block_time,
      block_number,
      transaction_hash,
      log_index,
      contract_address,
      CASE
        WHEN cardinality(argument_values) = 0 THEN LOWER(CONCAT('0x', SUBSTR(topics [2], -40)))
        ELSE LOWER(argument_values [1])
      END AS token0,
      CASE
        WHEN cardinality(argument_values) = 0 THEN LOWER(CONCAT('0x', SUBSTR(topics [3], -40)))
        ELSE LOWER(argument_values [2])
      END AS token1,
      CASE
        WHEN cardinality(argument_values) = 0 THEN LOWER(CONCAT('0x', SUBSTR(DATA, 27, 40)))
        ELSE LOWER(argument_values [2])
      END AS pair,
      block_date AS data_creation_date
    FROM
      zksync_mainnet.logs
    WHERE
      TRY(topics [1]) = '0x0d3648bd0f6ba80134a33ba9275ac585d9d315f0ad8355cddefde31afa28d0e9'
  ),
  v3_pairs AS (
    SELECT
      block_time,
      block_number,
      transaction_hash,
      log_index,
      contract_address,
      CASE
        WHEN cardinality(argument_values) = 0 THEN LOWER(CONCAT('0x', SUBSTR(topics [2], -40)))
        ELSE LOWER(argument_values [1])
      END AS token0,
      CASE
        WHEN cardinality(argument_values) = 0 THEN LOWER(CONCAT('0x', SUBSTR(topics [3], -40)))
        ELSE LOWER(argument_values [2])
      END AS token1,
      CASE
        WHEN cardinality(argument_values) = 0 THEN LOWER(CONCAT('0x', SUBSTR(DATA, 27, 40)))
        ELSE LOWER(argument_values [2])
      END AS pair,
      block_date AS data_creation_date
    FROM
      zksync_mainnet.logs
    WHERE
      TRY(topics [1]) = '0x783cca1c0412dd0d695e784568c96da2e9c22ff989357a2e8b1d9b2b4e6b7118'
  ),
  all_pairs AS (
    SELECT
      *,
      'Uniswap V2 Like' AS TYPE
    FROM
      v2_pairs
    UNION DISTINCT
    SELECT
      *,
      'Uniswap V3 Like' AS TYPE
    FROM
      v3_pairs
  )
SELECT
  COUNT(DISTINCT pair) AS pairs_created,
  TYPE
FROM
  all_pairs
GROUP BY
  2
ORDER BY
  1 DESC

NFT Activity

WITH
  nft_transfers AS (
    SELECT
      block_time,
      transaction_hash,
      contract_address,
      from_address,
      to_address
    FROM
      zksync_mainnet.erc1155_evt_transfer_batch
    UNION DISTINCT
    SELECT
      block_time,
      transaction_hash,
      contract_address,
      from_address,
      to_address
    FROM
      zksync_mainnet.erc1155_evt_transfer_single
    UNION DISTINCT
    SELECT
      block_time,
      transaction_hash,
      contract_address,
      from_address,
      to_address
    FROM
      zksync_mainnet.erc721_evt_transfer
  )
SELECT
  DATE(block_time) AS date_time,
  COUNT(DISTINCT transaction_hash) AS number_txns,
  COUNT(DISTINCT from_address) AS nft_active_from_addr,
  COUNT(DISTINCT to_address) AS nft_active_to_addr,
  CASE
    WHEN from_address = '0x0000000000000000000000000000000000000000' THEN 'Mint'
    WHEN to_address = '0x0000000000000000000000000000000000000000' THEN 'Burn'
    ELSE 'Transfer'
  END AS label
FROM
  nft_transfers
GROUP BY
  1,
  5
ORDER BY
  date_time ASC

Contract Development

WITH
  contract_creations AS (
    SELECT
      DATE(l.block_time) AS DATE,
      LOWER(SUBSTR(l.topics [4], 13, 64)) AS contract_address,
      LOWER(t.from_address) AS creator_address
    FROM
      zksync_mainnet.logs AS l
      INNER JOIN zksync_mainnet.transactions AS t ON l.transaction_hash = t.hash
    WHERE
      contract_address = '0x0000000000000000000000000000000000008006'
      AND try(l.topics [1]) = '0x290afdae231a3fc0bbae8b1af63698b0a1d79b21ad17df0342dfb952fe74f8e5'
      AND DATA = '0x'
  )
SELECT
  *,
  SUM(num_creations) OVER (
    ORDER BY
      block_date
  ) AS cumulative_creations,
  SUM(num_creators) OVER (
    ORDER BY
      block_date
  ) AS cumulative_creators
FROM
  (
    SELECT
      DATE AS block_date,
      COUNT(DISTINCT contract_address) AS num_creations,
      COUNT(DISTINCT creator_address) AS num_creators
    FROM
      contract_creations
    GROUP BY
      1
  )
ORDER BY
  1

📘Example: You can find an analytical dashboard with zkSync metrics here. To obtain a similar analysis for a different chain, simply change the underlying table in the SQL code provided in this use case.

Key Metrics for L1/L2 Networks

📘
Example: You can find an analytical dashboard with zkSync metrics here. To obtain a similar analysis for a different chain, simply change the underlying table in the SQL code provided in this use case.