Query editor
WITHdate_range AS (SELECTDATE_TRUNC('day',GREATEST(NOW() - interval '211' day, cast('2021-11-12' as date) )) AS start_time,DATE_TRUNC('day',GREATEST(NOW() - interval '180' day, cast('2021-11-12' as date) )) AS chart_start,cast( NOW() as date) AS end_time), get_system_addresses AS (SELECTblockchain,address, name, categoryFROM labels.system_addresses chains-- CROSS JOIN unnest(blockchain) as chains(blockchain)), eth_transfer_fees AS ( --h/t: https://ethereumdev.io/explore-ethereum-data-with-sql-queries-on-dune-analytics/SELECT --combine datasets if it falls in ovm1dt,cast(SUM(num_txs_per_day) as double) AS num_txs_per_day, cast(MAX(num_users_per_day) as double) AS num_users_per_day,--max to avoid double-countingcast(SUM(l1_gas_used) as double) AS l1_gas_used, cast(SUM(l2_gas_used) as double) AS l2_gas_used, cast(AVG(avg_l1_gas_price) as double) AS avg_l1_gas_price,cast(SUM(l2_gas_fees+l1_gas_fees) as double) AS eth_fees_per_day,cast(SUM(l2_gas_fees_usd+l1_gas_fee_usd) as double) AS eth_fees_per_day_usd,cast(SUM( equivalent_l1_tx_fee - (l2_gas_fees+l1_gas_fees) ) as double) AS eth_fees_saved_per_dayFROM (SELECTDATE_TRUNC('day',tx.block_time) AS dt,COUNT(*) AS num_txs_per_day,COUNT(DISTINCT "from") AS num_users_per_day,SUM(CASE WHEN gas_price = cast(0 as uint256) THEN 0 ELSE cast(gas_used as double)*cast(gas_price as double)/1e18 END) AS l2_gas_fees,SUM(CASE WHEN gas_price = cast(0 as uint256) THEN 0 ELSE cast(l1_fee as double) /1e18 END) --if l2 gas price = 0, then all 0AS l1_gas_fees,SUM(CASE WHEN gas_price = cast(0 as uint256) THEN 0 ELSE price*cast(gas_used as double)*cast(gas_price as double)/1e18 END) AS l2_gas_fees_usd,SUM(CASE WHEN gas_price = cast(0 as uint256) THEN 0 ELSE price*cast(l1_fee as double)/1e18 END) --if l2 gas price = cast(0 as uint256, then all 0AS l1_gas_fee_usd,SUM((cast(l1_gas_price as double)/1e9)*cast(gas_used as double))/SUM(cast(gas_used as double)) AS avg_l1_gas_price,SUM(CASE WHEN tx.gas_price >cast(0 as uint256) THEN cast(gas_used as double)*cast(tx.gas_price as double)/1e9 ELSE NULL END)/ SUM(CASE WHEN tx.gas_price > cast(0 as uint256) THEN gas_used ELSE NULL END) AS avg_l2_gas_price --if not free,SUM(l1_gas_used) AS l1_gas_used,SUM(gas_used) AS l2_gas_used,SUM(CASE WHEN gas_price = cast(0 as uint256) THEN 0 ELSE cast(gas_used as double)*cast(l1_gas_price*1e9 as double) END)/1e18 AS equivalent_l1_tx_feeFROM optimism.transactions txLEFT JOIN prices.usd p