Query editor
WITH fees AS (SELECTDATE_TRUNC('{{Select Date Granularity}}', day) AS month,name,SUM(data_fee_usd) AS l1_calldata_cost_usd,SUM(verification_fee_usd) AS l1_verify_cost_usd,SUM(blob_fee_usd) AS l1_blob_fees, -- Blob fees included as other feesSUM(l1_fee_usd) AS l1_total_fee_usdFROM rollup_economics_ethereum.l1_feesWHERE name = 'base'GROUP BY 1, 2),rev AS (SELECTDATE_TRUNC('{{Select Date Granularity}}', day) AS month,name,SUM(l2_rev_usd) AS l2_rev_usdFROM rollup_economics_ethereum.l2_revenueWHERE name = 'base'GROUP BY 1, 2),calc AS (SELECTf.month,-f.l1_calldata_cost_usd AS l1_calldata_cost_usd,-f.l1_verify_cost_usd AS l1_verify_cost_usd,-f.l1_blob_fees AS l1_blob_fees,COALESCE(r.l2_rev_usd, 0) AS l2_rev_usd,CASEWHEN r.l2_rev_usd IS NOT NULL THEN r.l2_rev_usd - f.l1_calldata_cost_usd - f.l1_verify_cost_usd - f.l1_blob_feesELSE 0END AS margin_usdFROM fees fLEFT JOIN rev r ON (f.month = r.month AND f.name = r.name))SELECTc.month,c.l1_calldata_cost_usd,c.l1_verify_cost_usd,c.l1_blob_fees,c.l2_rev_usd,c.margin_usd,SUM(c.l1_calldata_cost_usd) OVER (ORDER BY c.month) AS cumulative_l1_calldata_cost_usd,SUM(c.l1_verify_cost_usd) OVER (ORDER BY c.month) AS cumulative_l1_verify_cost_usd,SUM(c.l1_blob_fees) OVER (ORDER BY c.month) AS cumulative_l1_blob_fees,SUM(c.l2_rev_usd) OVER (ORDER BY c.month) AS cumulative_l2_rev_usd,SUM(c.margin_usd) OVER (ORDER BY c.month) AS cumulative_margin_usd,CASEWHEN SUM(c.l2_rev_usd) OVER (ORDER BY c.month) != 0 THEN (SUM(c.margin_usd) OVER (ORDER BY c.month) / SUM(c.l2_rev_usd) OVER (ORDER BY c.month)) * 100ELSE 0END AS cumulative_margin_percentageFROM calc cORDER BY c.month DESC;