Query editor
with raw as (SELECTl1_gas_paid_per_tx_legacy,l1_gas_paid_per_tx_bedrock,gas_fee_eth_per_tx_legacy, gas_fee_eth_per_tx_bedrock,gas_fee_usd_per_tx_legacy, gas_fee_usd_per_tx_bedrock,total_l1_gas_paid_saved, total_saved_gas_fees_eth, total_saved_gas_fees_usd, l1_gas_paid_per_tx_legacy - l1_gas_paid_per_tx_bedrock AS l1_gas_paid_savings_per_tx, (l1_gas_paid_per_tx_legacy - l1_gas_paid_per_tx_bedrock) / l1_gas_paid_per_tx_legacy AS pct_l1_gas_paid_savings_per_tx, gas_fee_eth_per_tx_legacy - gas_fee_eth_per_tx_bedrock AS gas_fee_eth_savings_per_tx, (gas_fee_eth_per_tx_legacy - gas_fee_eth_per_tx_bedrock) / gas_fee_eth_per_tx_legacy AS pct_gas_fee_eth_savings_per_tx, gas_fee_usd_per_tx_legacy - gas_fee_usd_per_tx_bedrock AS gas_fee_usd_savings_per_tx, (gas_fee_usd_per_tx_legacy - gas_fee_usd_per_tx_bedrock) / gas_fee_usd_per_tx_legacy AS pct_gas_fee_usd_savings_per_txFROM (SELECTcast( SUM(CASE WHEN migration_status = 'Legacy System' THEN l1_gas_paid ELSE NULL END) as double)/cast( SUM(CASE WHEN migration_status = 'Legacy System' THEN num_txs ELSE NULL END) as double) AS l1_gas_paid_per_tx_legacy, cast( SUM(CASE WHEN migration_status = 'Bedrock System' THEN l1_gas_paid ELSE NULL END) as double)/cast( SUM(CASE WHEN migration_status = 'Bedrock System' THEN num_txs ELSE NULL END) as double) AS l1_gas_paid_per_tx_bedrock, cast( SUM(CASE WHEN migration_status = 'Legacy System' THEN gas_fees_eth ELSE NULL END) as double)/cast( SUM(CASE WHEN migration_status = 'Legacy System' THEN num_txs ELSE NULL END) as double) AS gas_fee_eth_per_tx_legacy, cast( SUM(CASE WHEN migration_status = 'Bedrock System' THEN gas_fees_eth ELSE NULL END) as double)/cast( SUM(CASE WHEN migration_status = 'Bedrock System' THEN num_txs ELSE NULL END) as double) AS gas_fee_eth_per_tx_bedrock, cast( SUM(CASE WHEN migration_status = 'Legacy System' THEN gas_fees_usd ELSE NULL END) as double)/cast( SUM(CASE WHEN migration_status = 'Legacy System' THEN num_txs ELSE NULL END) as double) AS gas_fee_usd_per_tx_legacy, cast( SUM(CASE WHEN migration_status = 'Bedrock System' THEN gas_fees_usd ELSE NULL END) as double)/cast( SUM(CASE WHEN migration_status = 'Bedrock System' THEN num_txs ELSE NULL END) as double) AS gas_fee_usd_per_tx_bedrock, cast( SUM(CASE WHEN migration_status = 'Legacy System' THEN num_user_txs ELSE NULL END) as double) AS total_txs_legacy, cast( SUM(CASE WHEN migration_status = 'Bedrock System' THEN num_user_txs ELSE NULL END) as double) AS total_txs_bedrock, cast( SUM(saved_l1_gas_paid) as double) AS total_l1_gas_paid_saved, cast( SUM(saved_gas_fees_eth) as double) AS total_saved_gas_fees_eth, cast( SUM(saved_gas_fees_usd) as double) AS total_saved_gas_fees_usdFROM query_2614025--2608623 --https://dune.com/queries/2608623 --OP - Bedrock Migration Observability) a)SELECTrow_order,label, savings_per_tx, pct_savings_per_tx, total_savings, '|' as col,legacy_avg_per_tx, bedrock_avg_per_tx,pct_savings_per_tx* 100.0 AS pct_savings_per_tx_100pctFROM (SELECT1 as row_order,'L1 Gas Paid by Users' AS label,total_l1_gas_paid_saved AS total_savings,l1_gas_paid_savings_per_tx AS savings_per_tx,pct_l1_gas_paid_savings_per_tx AS pct_savings_per_tx,l1_gas_paid_per_tx_legacy AS legacy_avg_per_tx,l1_gas_paid_per_tx_bedrock AS bedrock_avg_per_txFROM rawUNION ALL