Query editor
WITH prices AS (SELECTDATE_TRUNC('day',minute) AS time,AVG(price) AS eth_priceFROM prices.usdWHERE symbol = 'WETH' AND minute BETWEEN CURRENT_DATE - 720 AND DATE_TRUNC('day',NOW())GROUP BY 1),txn_fees AS (SELECT*,SUM(txn_fees_usd / 1000000000) OVER(ORDER BY time) AS ttm_fees,AVG(txn_fees_usd * 12) OVER(ORDER BY time ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS annualized_30d_feesFROM (SELECTsub.*,txn_fees * eth_price AS txn_fees_usdFROM (SELECTDATE_TRUNC('day',block_time) AS time,SUM(CASEWHEN (type = 'Legacy' OR type = 'AccessList') THEN gas_price * t.gas_used / 1e18WHEN type = 'DynamicFee'AND base_fee_per_gas + max_priority_fee_per_gas <= max_fee_per_gas THEN (base_fee_per_gas + COALESCE(max_priority_fee_per_gas,0)) * t.gas_used / 1e18WHEN type = 'DynamicFee'AND base_fee_per_gas + max_priority_fee_per_gas > max_fee_per_gas THEN (max_fee_per_gas * t.gas_used) / 1e18END) AS txn_feesFROM ethereum.transactions tLEFT join ethereum.blocks b ON block_number = numberWHERE block_time BETWEEN '{{start_date_financials}}' AND DATE_TRUNC('day',NOW())GROUP BY 1)subLEFT JOIN prices p ON sub.time = p.time)sub2ORDER BY 1 DESC)SELECT*,CASE WHEN '{{currency_financials}}' = 'ETH' THEN txn_feesWHEN '{{currency_financials}}' = 'USD' THEN txn_fees_usdEND AS for_chartFROM txn_feesWHERE time >= '{{start_date_financials}}'