Skip to main content

Query Walkthrough

This section goes through the SQL query written by M3TA

Dashboard: the OP-Mainnet Economics dashboard.

1. L2 Revenue​

This SQL query is all about understanding transactions on the Layer 2 (L2) of a blockchain called Optimism over the last 180 days. Layer 2 is like a secondary layer on top of the main blockchain (Layer 1) which can help speed up transactions and reduce fees.

l2_rev, is calculating various metrics associated with Layer 2 (L2) revenue. It includes measures such as the number of transactions, revenue in ETH, revenue in USD, gas used, average gas price, and other related metrics. It fetches data from the optimism.transactions table, joins with the optimism.blocks table, and then also incorporates price data from the prices.usd table. It filters records from the last 180 days.

WITH l2_rev AS (
SELECT
    DATE_TRUNC('DAY',block_time) AS dt,
    cast(SUM(case when gas_price > cast(0 as uint256) THEN 1 ELSE 0 END) as double) AS txs, cast( COUNT(DISTINCT "from") as double) AS addr,
    cast(SUM(
        CASE WHEN gas_price = cast(0 as uint256) THEN 0 ELSE --deposits & gas oracle
        cast(l1_fee + t.gas_used*gas_price as double)/1e18
        END
        ) as double) AS l2_eth_rev,
    SUM(price*cast(l1_fee + t.gas_used*gas_price as double)/1e18) AS l2_eth_rev_usd,
    cast(SUM((l1_gas_used)
        ) as double) AS l1_gas_used_on_l2,
    cast(SUM(t.gas_used) as double) AS l2_gas_used,
    cast(SUM(gas_price) as double) AS sum_l2_gas_price,
    AVG(cast(gas_price as double)/1e9) AS avg_l2_gas_price,
    AVG(cast(l1_gas_price as double)/1e9) AS avg_l1_gas_price_on_l2,
    SUM(
    
    cast(l1_gas_used-( --subtract fixed overhead to get calldata & noncalldata of each tx
                CASE
                WHEN block_number <= 2071714 THEN 2750.0
                WHEN block_time <= cast('2023-06-06 16:30:00' as timestamp) THEN 2100.0 --bedrock at 16:00, but set a buffer
                ELSE 188
                -- todo add bedrock
                END
                )
    as double)) AS calldata_gas_l2
    
    , SUM(cast(l1_fee as double) / 1e18 ) AS l2_eth_rev_l1_contrib
    , SUM(cast(gas_price*t.gas_used as double) / 1e18 ) AS l2_eth_rev_l2_contrib
    , SUM(cast(COALESCE(base_fee_per_gas,cast(gas_price as double) )*t.gas_used as double) / 1e18 ) AS l2_eth_rev_l2_contrib_base --if no base fee (legacy), take gas price
    , SUM(case when gas_price = cast(0 as uint256) then 0 else
        cast( (cast(gas_price as double)-base_fee_per_gas)*t.gas_used as double) / 1e18 end) AS l2_eth_rev_l2_contrib_priority
        
    , SUM(price* (cast(l1_fee as double) / 1e18 ) ) AS l2_eth_rev_l1_contrib_usd
    , SUM(price* (cast(gas_price*t.gas_used as double) / 1e18 ) ) AS l2_eth_rev_l2_contrib_usd
    , SUM(price* cast(COALESCE(base_fee_per_gas,cast(gas_price as double) )*t.gas_used as double) / 1e18 ) AS l2_eth_rev_l2_contrib_base_usd --if no base fee (legacy), take gas price
    , SUM(price* (case when gas_price = cast(0 as uint256) then 0 else
        cast( (cast(gas_price as double)-base_fee_per_gas)*t.gas_used as double) / 1e18 end) ) AS l2_eth_rev_l2_contrib_priority_usd
        
    ,cast(SUM(bytearray_length(data) ) as double) AS total_bytes_l2
    ,COUNT(DISTINCT b.number) AS num_blocks
    
    FROM optimism.transactions t
        INNER JOIN optimism.blocks b
            ON b.number = t.block_number
            AND b.time = t.block_time
            AND b.time > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
        LEFT JOIN prices.usd p
        ON DATE_TRUNC('minute',t.block_time) = p.minute
        AND p.blockchain = 'ethereum'
        AND p.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 --L1 WETH
    WHERE block_time > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
        
    GROUP BY 1
)
  1. Transactions Count (txs): It counts how many transactions had a gas price greater than zero on each day. Think of "gas" as the fee you pay to make a transaction.
  2. Unique Addresses (addr): It counts how many unique users (or addresses) made transactions each day.
  3. Revenue in ETH (l2_eth_rev & l2_eth_rev_usd): It calculates the total earnings in both Ethereum's currency (ETH) and its equivalent in US dollars (USD) for each day. This is based on the transaction fees users paid.
  4. Call Data Gas (calldata_gas_l2): This is about understanding a specific type of transaction detail called "call data". It tries to find out how much gas was used for this data.

Data Table​

  • Main data about transactions is taken from a table called optimism.transactions.
  • To ensure accuracy, it's cross-checked with another table called optimism.blocks.
  • To get the USD value of ETH at any given time, it uses data from the prices.usd table.

2. L1 Cost​

This query focuses on Layer 1 (L1) expenses on the Ethereum blockchain while operating the OP-Mainnet over the past 180 days. The objective is to determine the cost (in ETH) of performing specific operations on this primary layer.

l1_exp AS (
SELECT DATE_TRUNC('DAY',t.block_time) AS dt,
            SUM(cast(gas_used*cast(gas_price as double)/1e18 as double)) AS l1_eth_exp,
            SUM(price*cast(gas_used*cast(gas_price as double)/1e18 as double)) AS l1_eth_exp_usd,
            SUM(CASE WHEN submitter = 'CTC' THEN cast(gas_used*cast(gas_price as double)/1e18 as double) ELSE 0 END) AS l1_eth_exp_ctc,
            SUM(CASE WHEN submitter = 'SCC' THEN cast(gas_used*cast(gas_price as double)/1e18 as double) ELSE 0 END) AS l1_eth_exp_scc, 
            SUM(cast(gas_used as double)) AS l1_gas_used,
            SUM(CASE WHEN submitter = 'CTC' THEN cast(gas_used as double) ELSE 0 END) AS l1_gas_used_ctc,
            SUM(CASE WHEN submitter = 'SCC' THEN cast(gas_used as double) ELSE 0 END) AS l1_gas_used_scc,
            SUM(CASE WHEN submitter = 'CTC' THEN 1.0 ELSE 0.0 END) AS num_batches_ctc,
            SUM(CASE WHEN submitter = 'SCC' THEN 1.0 ELSE 0.0 END) AS num_batches_scc
                         
    FROM
        (
        --Optimism 2.0
        SELECT
        evt_tx_hash, evt_block_time AS block_time,evt_block_number, 'Optimism' AS chain, 'OVM 2.0' AS version, 'CTC' AS submitter
        FROM optimism_ethereum.CanonicalTransactionChain_evt_TransactionBatchAppended
        WHERE evt_block_time > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
        AND evt_block_time <= cast('2023-06-06 16:30:00' as timestamp) --bedrock 16:00 but set a buffer
        
        UNION ALL
        
        SELECT
        evt_tx_hash, evt_block_time,evt_block_number, 'Optimism' AS chain, 'OVM 2.0' AS version, 'SCC' AS submitter
        FROM optimism_ethereum.StateCommitmentChain_evt_StateBatchAppended
        WHERE evt_block_time > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
        AND evt_block_time <= cast('2023-06-06 16:30:00' as timestamp) --bedrock 16:00 but set a buffer
        
        -- bedrock
        UNION ALL
        
        SELECT
        hash, block_time ,block_number, 'Optimism' AS chain, 'Bedrock' AS version, 'CTC' AS submitter
        FROM ethereum.transactions
        WHERE block_time > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
            AND block_time > cast('2023-06-06 16:30:00' as timestamp) --bedrock 16:00 but set a buffer
            AND to = 0xFF00000000000000000000000000000000000010 --batchinbox to
            AND "from" = 0x6887246668a3b87f54deb3b94ba47a6f63f32985 --batchinbox from
        
        UNION ALL
        
        SELECT
        hash, block_time, block_number, 'Optimism' AS chain, 'Bedrock' AS version, 'SCC' AS submitter
        FROM ethereum.transactions
        WHERE block_time > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
            AND block_time > cast('2023-06-06 16:30:00' as timestamp) --bedrock 16:00 but set a buffer
            AND to = 0xdfe97868233d1aa22e815a266982f2cf17685a27--outputoracle proxy contract --known june 5
            AND "from" = 0x473300df21d047806a082244b417f96b32f13a33 --outputoracle submitter
            
        
        ) subm
    INNER JOIN ethereum.transactions t
        ON subm.evt_tx_hash = t.hash
        AND subm.evt_block_number = t.block_number
        AND t.block_time > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
    LEFT JOIN prices.usd p
        ON DATE_TRUNC('minute',t.block_time) = p.minute
        AND p.blockchain = 'ethereum'
        AND p.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 --L1 WETH
        AND p.minute > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
    WHERE t.block_time > DATE_TRUNC('DAY',NOW()) - interval '180' DAY
    GROUP BY 1
)
  1. Expenses in ETH and USD:
    • l1_eth_exp: Total expenses in ETH.
    • l1_eth_exp_usd: The equivalent of those expenses in USD.
  2. Expenses by Submitter Type:
    • l1_eth_exp_ctc & l1_gas_used_ctc: Expenses in ETH and gas used when submitted to BatchInbox.
    • l1_eth_exp_scc & l1_gas_used_scc: Expenses in ETH and gas used when submitted to L2OutputRoot.
  3. Total Gas Used:
    • l1_gas_used: Total amount of gas used for these operations.
  4. Number of Batches:
    • num_batches_ctc: How many transaction batches were submitted to BatchInbox.
    • num_batches_scc: How many state roots were submitted to L2OutputRoot.

3. Final Analysis​

SELECT * 
,l2_eth_rev - l1_eth_exp AS profit_eth
,l2_eth_rev_usd - l1_eth_exp_usd AS profit_usd
,l2_eth_rev_l1_contrib - l1_eth_exp AS prof_l1_contrib
,l2_eth_rev_l1_contrib_usd - l1_eth_exp_usd AS prof_l1_contrib_usd
,(l2_eth_rev - l1_eth_exp)/l2_eth_rev AS prof_margin_percentage
, CASE WHEN l1_exp.dt >= cast('2023-06-07' as timestamp) THEN 5000000.0 ELSE 0 END AS l2_gas_target_per_block
FROM l1_exp
FULL OUTER JOIN l2_rev ON l2_rev.dt = l1_exp.dt
WHERE l1_exp.dt > DATE_TRUNC('DAY',NOW()) - interval '180' DAY

The query is aimed at computing profit-related metrics for the Optimism blockchain. It uses data from two pre-defined sets (l1_exp and l2_rev) to determine revenues, expenses, and profits in both Ethereum (ETH) and US dollars (USD).

  1. Profit Calculations in ETH (profit_eth ): This calculates the profit in ETH by subtracting L1 expenses from L2 revenue.
  2. Profit from Layer 1 Contributions( prof_l1_contrib ): This calculates the profit from Layer 1 contributions in ETH.
  3. Profit Margin Percentage (prof_margin_percentage ): This calculates what percentage of the revenue is profit. If you earned $100 and had $80 in expenses, your profit margin percentage would be 20%.
CTRL + J