Cross-Chain Use Case
Building a Dashboard with Chain Overview Metrics.
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 9 months ago