Skip to main content

Query Walkthrough

This section goes through the SQL query written by M3TA.

Dashboard: the zkSync Era Economics dashboard.

1. Revenue​

1.1 L2 Fee Revenue​

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

This query retrieves the l2_fees as part of an SQL query that calculates Ethereum revenue based on gas usage and gas price. It is performed on the zksync_web3_transactions database table and calculates daily revenue (l2_rev_eth) by multiplying the gas used by the gas price.

1.2 Profit from L1 Tx for L2 Tx initiation​

with temp_table AS (SELECT block_timestamp, `hash` as tx_hash,value/1e18 AS value_formatted
,from_address, to_address,receipt_gas_used, gas_price
,substr(input, 77,62) AS hex_l2Value
,SAFE_CAST(`ardent-window-384305`.meta.from_hex_to_intstring(CONCAT('0x',substr(input, 77,62))) AS FLOAT64)/1e18 AS l2value_formmatted
FROM `bigquery-public-data.crypto_ethereum.transactions`
WHERE TIMESTAMP_TRUNC(block_timestamp, DAY) >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -160 DAY)
AND to_address = LOWER('0x32400084c286cf3e17e7b677ea9583e60a000324') --DiamondProxy
AND Substring(input,0,10) = '0xeb672419' --Request l2Trs
AND receipt_status = 1
),

l1_request_l2_tx_fee AS ( 
  SELECT TIMESTAMP_TRUNC(block_timestamp, DAY) as day
        ,SUM(value_formatted) - SUM(l2value_formmatted) AS request_l2_tx_fee 
  FROM `temp_table`
  GROUP BY 1
),

The data is processed further to calculate a specific kind of fee, namely the fee associated with Layer 1 requests for Layer 2 transactions. The calculated fee is stored as l1_request_l2_tx_fee.

2. Cost​

l1_verifier_fee AS (
    SELECT TIMESTAMP_TRUNC(block_timestamp, DAY) as day
        ,SUM((receipt_gas_used *gas_price)/1e18) AS l1_proveBlock_fee_eth
  FROM `bigquery-public-data.crypto_ethereum.transactions`
  WHERE TIMESTAMP_TRUNC(block_timestamp, DAY) >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -160 DAY)
  AND from_address = LOWER('0x3527439923a63F8C13CF72b8Fe80a77f6e572092') --zkSync Era Validator
  AND to_address = LOWER('0x3dB52cE065f728011Ac6732222270b3F2360d919') --ValidatorTimelock
  AND Substring(input,0,10) = '0x7739cbe7' --proveBlocks
  AND receipt_status = 1
  GROUP BY 1
),

l1_commit_block_fee AS (
    SELECT TIMESTAMP_TRUNC(block_timestamp, DAY) as day
        ,SUM((receipt_gas_used *gas_price)/1e18)  As l1_commitBlock_fee_eth
  FROM `bigquery-public-data.crypto_ethereum.transactions`
  WHERE TIMESTAMP_TRUNC(block_timestamp, DAY) >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -160 DAY)
  AND to_address = LOWER('0x3dB52cE065f728011Ac6732222270b3F2360d919') --ValidatorTimelock
  AND Substring(input,0,10) = '0x0c4dd810' --commitBlocks
  AND receipt_status = 1
  GROUP BY 1
),

l1_execute_block_fee AS (
    SELECT TIMESTAMP_TRUNC(block_timestamp, DAY) as day
        ,SUM((receipt_gas_used *gas_price)/1e18)  As l1_executeBlock_fee_eth
  FROM `bigquery-public-data.crypto_ethereum.transactions`
  WHERE TIMESTAMP_TRUNC(block_timestamp, DAY) >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -160 DAY)
  AND from_address = LOWER('0x3527439923a63F8C13CF72b8Fe80a77f6e572092') --zkSync Era Validator
  AND to_address = LOWER('0x3dB52cE065f728011Ac6732222270b3F2360d919') --ValidatorTimelock
  AND Substring(input,0,10) = '0xce9dcf16' --executeBlocks
  AND receipt_status = 1
  GROUP BY 1
),

The ExecutorFacet is a contract that accepts Layer 2 blocks and verifies their zk-proofs. The state transition for the contract is divided into three stages: commitBlocks, proveBlocks, and executeBlocks.

During the commitBlocks stage, the contract checks the timestamp of the Layer 2 block, processes the Layer 2 logs, saves data for the block, and prepares data for the zk-proof. During the proveBlocks stage, the contract validates the zk-proof. Finally, during the executeBlocks stage, the contract finalizes the state, marks the Layer 1 to Layer 2 communication processing, and saves the Merkle tree with the Layer 2 logs.

Source: era-contracts/docs/Overview.md at main · matter-labs/era-contracts

3. Final Analysis​

SELECT * 
,l2_fees + request_l2_tx_fee AS l1_l2_rev
,l1_proveBlock_fee_eth + l1_commitBlock_fee_eth + l1_executeBlock_fee_eth AS l1_cost_eth
,(l1_proveBlock_fee_eth + l1_commitBlock_fee_eth + l1_executeBlock_fee_eth)* eth_price AS l1_cost_usd
, l2_fees + request_l2_tx_fee - (l1_proveBlock_fee_eth + l1_commitBlock_fee_eth + l1_executeBlock_fee_eth ) AS seq_profit_eth
,(l2_fees + request_l2_tx_fee - (l1_proveBlock_fee_eth + l1_commitBlock_fee_eth + l1_executeBlock_fee_eth ))*eth_price AS seq_profit_usd
FROM fee_breakdown
ORDER BY day ASC
CTRL + J