Skip to main content

Query Walkthrough

This section goes through the SQL query written by M3TA.

Dashboard: the Polygon zkEVM Economics dashboard.

1. Sequencer​

1.1 Sequencer Revenue: Understanding L2 Transaction Fees Revenue in ETH (l2_fee)​

l2_fee AS (SELECT DATE(TIMESTAMP_SECONDS(block_timestamp)) as day,
        SUM(CAST(receipt_gas_used*gas_price AS INT64)/1e18) AS l2_rev_eth -- l2_tx_fee
 FROM `ardent-window-384305.meta.zkevm_web3_transactions` 
 WHERE TIMESTAMP_TRUNC(TIMESTAMP_SECONDS(block_timestamp),DAY)  >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -120 DAY)
 GROUP BY 1
 ),

This SQL query is used to calculate the daily revenue generated from L2 transaction fees, denominated in Ethereum (ETH). The data source for this query is the zkevm_web3_transactions table, which presumably logs L2 Ethereum transactions. The query segments the data by day (for the last 120 days) and sums up the product of receipt_gas_used and gas_price, giving the total transaction fee in ETH.

1.2 Sequencer Cost: L1 Sequencer Transaction Fees Cost in ETH (L1SeqTxGasFees)​

L1SeqTxGasFees AS ( SELECT DATE(block_timestamp) AS day
,SUM(receipt_gas_used*gas_price/1e18) AS l1_seq_tx_fees_eth
FROM `bigquery-public-data.crypto_ethereum.transactions`
WHERE TIMESTAMP_TRUNC(block_timestamp, DAY) >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -120 DAY) 
AND substring(input,1,10)='0x5e9145c9' --sequence Batches
AND from_address = LOWER('0x148Ee7dAF16574cD020aFa34CC658f8F3fbd2800') --Sequencer contract
AND to_address = LOWER('0x5132a183e9f3cb7c848b0aac5ae0c4f0491b7ab2') --zkEVM Main contract
GROUP BY 1
),

This SQL query is used to calculate the daily gas fees that the sequencer incurs on the Ethereum main chain. The data source for this query is the crypto_ethereum.transactions table, which logs all Ethereum transactions. The query filters for specific transaction criteria, such as the input substring, from_address, and to_address, and then calculates the daily sum of transaction fees the sequencer pays. The objective of this query is to understand the daily gas fees that the sequencer pays on the Ethereum main chain.

1.3 Sequencer Cost: Batch Fees Cost in MATIC (batchFee)​

batchFee AS (Select DATE(block_timestamp) AS day, SUM(SAFE_CAST(value AS FLOAT64)/1e18) AS batch_fee_matic
FROM `bigquery-public-data.crypto_ethereum.token_transfers`
WHERE TIMESTAMP_TRUNC(block_timestamp, DAY) >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -120 DAY)
AND token_address = LOWER('0x7D1AfA7B718fb893dB30A3aBc0Cfc608AaCfeBB0') -- MATIC on ethereum
AND from_address = LOWER('0x148Ee7dAF16574cD020aFa34CC658f8F3fbd2800') --Sequencer
AND to_address = LOWER('0x5132a183e9f3cb7c848b0aac5ae0c4f0491b7ab2') --zkEVM Main contract
GROUP BY 1
),

This SQL query is used to understand the daily batch fees denominated in MATIC. The data source for this query is the crypto_ethereum.token_transfers table, which logs token transfer events on the Ethereum network. The query filters for MATIC token transfers between specific addresses and then aggregates these transfers by day. The objective of this query is to provide insights into the daily batch fees charged by the aggregator.

2.Aggregator​

2.1 Aggregator Revenue: Batch Rewards in MATIC (agg_batchFee)​

agg_batchFee AS (Select DATE(block_timestamp) AS day, SUM(SAFE_CAST(value AS FLOAT64)/1e18) AS agg_reward_matic
FROM `bigquery-public-data.crypto_ethereum.token_transfers`
WHERE TIMESTAMP_TRUNC(block_timestamp, DAY) >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -120 DAY)
AND token_address = LOWER('0x7D1AfA7B718fb893dB30A3aBc0Cfc608AaCfeBB0') -- MATIC on ethereum
AND from_address = LOWER('0x5132a183e9f3cb7c848b0aac5ae0c4f0491b7ab2') --zkEVM Main contract
AND to_address = LOWER('0xdA87c4a76922598Ac0272F4D9503a35071D686eA') --Aggregator
GROUP BY 1
),

This section describes the methodology used to calculate daily batch rewards for aggregator transactions on the Ethereum main chain. Aggregators in L2 solutions help collate multiple transactions into a single batch for posting to the main chain.

This SQL query describes the daily rewards given to the aggregator in $MATIC. The rewards are denominated in $MATIC and sent from the zkEVM main contract to the aggregator.

2.2 Aggregator Cost: L1 Aggregator Transaction Fees in ETH (L1AggTxGasFee)​

L1AggTxGasFee AS ( SELECT DATE(block_timestamp) AS day
,SUM(receipt_gas_used*gas_price)/1e18 AS l1_agg_tx_fees
FROM `bigquery-public-data.crypto_ethereum.transactions` 
WHERE TIMESTAMP_TRUNC(block_timestamp, DAY) >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -120 DAY)
AND substring(input,1,10)='0xa50a164b' --verify batches
AND from_address = LOWER('0xdA87c4a76922598Ac0272F4D9503a35071D686eA') --Aggregator
AND to_address = LOWER('0x5132a183e9f3cb7c848b0aac5ae0c4f0491b7ab2') --zkEVM Main contract
GROUP BY 1
),

The SQL query used to calculate daily gas fees is similar to the sequencer fee calculation, but with different filtering criteria. The crypto_ethereum.transactions table, which logs all Ethereum transactions, serves as the data source for this query. The query filters for specific transaction criteria, such as the input substring, from_address, and to_address, and then calculates the daily sum of transaction fees paid by the aggregator.

3. Final Analysis​

Select l.day,l2_rev_eth, l1_seq_tx_fees_eth, batch_fee_matic,matic_eth_ratio
,ROUND(l1_seq_tx_fees_eth + (batch_fee_matic*matic_eth_ratio),2) AS seq_l1_cost_eth
,ROUND(l2_rev_eth - l1_seq_tx_fees_eth + (batch_fee_matic*matic_eth_ratio),2) AS seq_profit_eth
,ROUND((l2_rev_eth - l1_seq_tx_fees_eth + (batch_fee_matic*matic_eth_ratio))*m.eth_price,2) AS seq_profit_usd
,l1_agg_tx_fees, agg_reward_matic
,agg_reward_matic*matic_eth_ratio AS agg_reward_eth
,ROUND((agg_reward_matic*matic_eth_ratio) - l1_agg_tx_fees,2) AS aggregator_profit_eth
,ROUND(((agg_reward_matic*matic_eth_ratio) - l1_agg_tx_fees)*eth_price,2) AS agg_profit_usd
FROM l2_fee l
LEFT JOIN L1SeqTxGasFees s ON l.day = s.day
LEFT JOIN batchFee b ON l.day = b.day
LEFT JOIN L1AggTxGasFee a ON l.day = a.day
LEFT JOIN agg_batchFee ab ON l.day = ab.day
LEFT JOIN matic_eth m ON l.day = m.day
ORDER BY l.day DESC
CTRL + J