Query editor
WITH optimism AS (SELECTdt, COUNT(*) AS op_txs_per_day, COUNT(DISTINCT "from") AS op_users_per_day, AVG(transaction_fee) AS op_transaction_feesFROM (SELECT*, DATE_TRUNC('day', block_time) AS dt, (CAST(gas_used AS DOUBLE) * CAST(gas_price AS DOUBLE) + l1_fee) / 10e17 AS transaction_feeFROM optimism.transactions)GROUP BY 1), arbitrum AS (SELECTDATE_TRUNC('day', block_time) AS dt, COUNT(*) AS arb_txs_per_day, COUNT(DISTINCT "from") AS arb_users_per_day, AVG(transaction_fee) AS arb_transaction_feesFROM (SELECT*, (CAST(gas_used AS DOUBLE) + CAST(gas_used_for_l1 AS DOUBLE)) * CAST(effective_gas_price AS DOUBLE) / 10e17 AS transaction_feeFROM arbitrum.transactions)GROUP BY 1), ethereum AS (SELECTDATE_TRUNC('day', block_time) AS dt, COUNT(*) AS eth_txs_per_day, COUNT(DISTINCT "from") AS eth_users_per_day, AVG(transaction_fee) AS eth_transaction_feesFROM (SELECT*, CASEWHEN (type = 'Legacy' OR type = 'AccessList') THEN CAST(gas_price AS DOUBLE) * CAST(t.gas_used AS DOUBLE) / 1e18WHEN type = 'DynamicFee' AND base_fee_per_gas + max_priority_fee_per_gas <= max_fee_per_gas THEN (CAST(base_fee_per_gas AS DOUBLE) + COALESCE(CAST(max_priority_fee_per_gas AS DOUBLE), 0)) * CAST(t.gas_used AS DOUBLE) / 1e18WHEN type = 'DynamicFee' AND base_fee_per_gas + max_priority_fee_per_gas > max_fee_per_gas THEN (CAST(max_fee_per_gas AS DOUBLE) * CAST(t.gas_used AS DOUBLE)) / 1e18END AS transaction_feeFROM ethereum.transactions AS tLEFT JOIN ethereum.blocks AS b ON block_number = number)GROUP BY 1), eth_price AS (SELECTDATE_TRUNC('day', minute) AS dt, AVG(price) AS eth_priceFROM prices.usdWHERE symbol = 'WETH'GROUP BY 1)