Query editor
WITHdate_range AS (SELECTDATE_TRUNC('month', NOW() - INTERVAL '13' month) AS start_time,NOW() AS end_time,DATE_TRUNC('month', NOW()) AS end_month),get_system_addresses AS (SELECTblockchain,address,name,categoryFROMlabels.system_addresses AS chains) /* CROSS JOIN unnest(blockchain) as chains(blockchain) */,l2_fees AS (/* combine datasets if it falls in ovm1 */SELECTdt,SUM(num_txs_per_month) AS num_txs_per_month,MAX(num_users_per_month) AS num_users_per_month,/* max to avoid double-counting */ SUM(l1_gas_used_on_l2) AS l1_gas_used_on_l2,SUM(l2_gas_used) AS l2_gas_used,AVG(avg_l1_gas_price) AS avg_l1_gas_price,AVG(avg_l2_gas_price) AS avg_l2_gas_price,SUM(l2_gas_fees + l1_gas_fees) AS eth_fees_per_monthFROM(SELECTDATE_TRUNC('month', block_time) AS dt,COUNT(*) AS num_txs_per_month,COUNT(DISTINCT "from") AS num_users_per_month,SUM(CASEWHEN gas_price = cast(0 as uint256) THEN 0ELSE (cast(gas_used as double) * cast(gas_price as double)) / CAST(1e18 AS DOUBLE)END) AS l2_gas_fees,SUM(CASEWHEN gas_price = cast(0 as uint256) THEN 0ELSE cast(l1_fee as double) / CAST(1e18 AS DOUBLE)END /* if l2 gas price = 0, then all 0 */) AS l1_gas_fees,SUM(l1_gas_price / 1e9 * gas_used) / CAST(SUM(gas_used) AS DOUBLE) AS avg_l1_gas_price,SUM(CASEWHEN gas_price > cast(0 as uint256) THEN cast(gas_used as double) * cast(gas_price as double) / 1e9ELSE NULLEND) / CAST(SUM(CASEWHEN gas_price > cast(0 as uint256) THEN gas_usedELSE NULLEND) AS DOUBLE) AS avg_l2_gas_price,/* if not free */ SUM(l1_gas_used) AS l1_gas_used_on_l2,