Home

Cross-Chain Use Case

Building a Dashboard with Chain Overview Metrics.

Suggest Edits

📘

The dashboard can be viewed here.

Step 1: Write the SQL query

In this use case, we're comparing the number of transactions, the number of active users, the transactions per unique active user and transaction fees across several networks (Ethereum, Arbitrum, Polygon, Polygon zkEVM, zkSync and Binance Smart Chain).

The query to obtain these metrics is:

WITH
  prices AS (
    SELECT
      data_creation_date AS date_time,
      AVG(price) AS price_usd,
      LOWER(symbol) AS symbol
    FROM
      prices.usd
    WHERE
      data_creation_date >= CURRENT_DATE - INTERVAL '31' DAY
      AND DATE(data_creation_date) < CURRENT_DATE
      AND symbol IN ('ETH', 'MATIC', 'BNB')
    GROUP BY
      1,
      3
  ),
  price_eth AS (
    SELECT
      *
    FROM
      prices
    WHERE
      symbol = 'eth'
  ),
  price_bnb AS (
    SELECT
      *
    FROM
      prices
    WHERE
      symbol = 'bnb'
  ),
  price_matic AS (
    SELECT
      *
    FROM
      prices
    WHERE
      symbol = 'matic'
  )
SELECT
  c.date_time,
  network,
  num_of_transactions,
  unique_active_users,
  num_transaction_per_uau,
  txn_fees,
  CASE
    WHEN network IN ('Ethereum', 'ZkSync', 'ZkEVM', 'Arbitrum') THEN pe.price_usd * txn_fees
    WHEN network = 'Polygon (PoS)' THEN pm.price_usd * txn_fees
    WHEN network = 'Binance Smart Chain' THEN pb.price_usd * txn_fees
    ELSE NULL
  END AS txn_fees_usd,
  CASE
    WHEN network IN ('Ethereum', 'ZkSync', 'ZkEVM', 'Arbitrum') THEN (pe.price_usd * txn_fees) / num_of_transactions
    WHEN network = 'Polygon (PoS)' THEN (pm.price_usd * txn_fees) / num_of_transactions
    WHEN network = 'Binance Smart Chain' THEN (pb.price_usd * txn_fees) / num_of_transactions
    ELSE NULL
  END AS fees_per_txn
FROM
  (
    SELECT
      block_date AS date_time,
      'Ethereum' AS network,
      COUNT(DISTINCT t.hash) AS num_of_transactions,
      COUNT(DISTINCT from_address) AS unique_active_users,
      CAST(COUNT(DISTINCT t.hash) AS DOUBLE) / CAST(COUNT(DISTINCT from_address) AS DOUBLE) AS num_transaction_per_uau,
      SUM((gas_price * gas_used) / POW(10, 18)) AS txn_fees
    FROM
      ethereum_mainnet.transactions AS t
    WHERE
      block_date >= CURRENT_DATE - INTERVAL '31' DAY
      AND DATE(block_date) < CURRENT_DATE
      AND STATUS = 1
    GROUP BY
      1,
      2
    UNION DISTINCT
    SELECT
      data_creation_date AS date_time,
      'Arbitrum' AS network,
      COUNT(DISTINCT t.hash) AS num_of_transactions,
      COUNT(DISTINCT from_address) AS unique_active_users,
      CAST(COUNT(DISTINCT t.hash) AS DOUBLE) / CAST(COUNT(DISTINCT from_address) AS DOUBLE) AS num_transaction_per_uau,
      SUM((gas_price * gas_used) / POW(10, 18)) AS txn_fees
    FROM
      arbitrum_mainnet.transactions AS t
    WHERE
      data_creation_date >= CURRENT_DATE - INTERVAL '31' DAY
      AND DATE(data_creation_date) < CURRENT_DATE
      AND STATUS = 1
    GROUP BY
      1,
      2
    UNION DISTINCT
    SELECT
      block_date AS date_time,
      'ZkSync' AS network,
      COUNT(DISTINCT t.hash) AS num_of_transactions,
      COUNT(DISTINCT from_address) AS unique_active_users,
      CAST(COUNT(DISTINCT t.hash) AS DOUBLE) / CAST(COUNT(DISTINCT from_address) AS DOUBLE) AS num_transaction_per_uau,
      SUM((gas_price * gas_used) / POW(10, 18)) AS txn_fees
    FROM
      zksync_mainnet.transactions AS t
    WHERE
      block_date >= CURRENT_DATE - INTERVAL '31' DAY
      AND DATE(block_date) < CURRENT_DATE
      AND STATUS = 1
    GROUP BY
      1,
      2
    UNION DISTINCT
    SELECT
      data_creation_date AS date_time,
      'Polygon ZkEVM' AS network,
      COUNT(DISTINCT t.hash) AS num_of_transactions,
      COUNT(DISTINCT from_address) AS unique_active_users,
      CAST(COUNT(DISTINCT t.hash) AS DOUBLE) / CAST(COUNT(DISTINCT from_address) AS DOUBLE) AS num_transaction_per_uau,
      SUM((gas_price * gas_used) / POW(10, 18)) AS txn_fees
    FROM
      polygon_zkevm_mainnet.transactions AS t
    WHERE
      data_creation_date >= CURRENT_DATE - INTERVAL '31' DAY
      AND DATE(data_creation_date) < CURRENT_DATE
      AND STATUS = 1
    GROUP BY
      1,
      2
    UNION DISTINCT
    SELECT
      data_creation_date AS date_time,
      'Binance Smart Chain' AS network,
      COUNT(DISTINCT t.hash) AS num_of_transactions,
      COUNT(DISTINCT from_address) AS unique_active_users,
      CAST(COUNT(DISTINCT t.hash) AS DOUBLE) / CAST(COUNT(DISTINCT from_address) AS DOUBLE) AS num_transaction_per_uau,
      SUM((gas_price * gas_used) / POW(10, 18)) AS txn_fees
    FROM
      bsc_mainnet.transactions AS t
    WHERE
      data_creation_date >= CURRENT_DATE - INTERVAL '31' DAY
      AND DATE(data_creation_date) < CURRENT_DATE
      AND STATUS = 1
    GROUP BY
      1,
      2
    UNION DISTINCT
    SELECT
      data_creation_date AS date_time,
      'Polygon (PoS)' AS network,
      COUNT(DISTINCT t.hash) AS num_of_transactions,
      COUNT(DISTINCT from_address) AS unique_active_users,
      CAST(COUNT(DISTINCT t.hash) AS DOUBLE) / CAST(COUNT(DISTINCT from_address) AS DOUBLE) AS num_transaction_per_uau,
      SUM((gas_price * gas_used) / POW(10, 18)) AS txn_fees
    FROM
      polygon_mainnet.transactions AS t
    WHERE
      data_creation_date >= CURRENT_DATE - INTERVAL '31' DAY
      AND DATE(data_creation_date) < CURRENT_DATE
      AND STATUS = 1
    GROUP BY
      1,
      2
  ) AS c
  INNER JOIN price_eth AS pe ON c.date_time = pe.date_time
  INNER JOIN price_bnb AS pb ON c.date_time = pb.date_time
  INNER JOIN price_matic AS pm ON c.date_time = pm.date_time
ORDER BY
  1 ASC

Step 2: Run the query and create visualizations

Step 3: Create a dashboard

Put all the visualizations together by adding them to a dashboard.

Step 4: Create an API to surface the data elsewhere

The dashboard does not have to be the full story. You can use the same SQL query to create an API and use to to surface super-fresh data to your app, or anywhere else you want!

And that’s it! Creating the API takes one click, and it’s ready!

Updated about 2 months ago