Query editor
WITH blockchains_filter AS (SELECT blockchainFROM evms.infoWHERE name = '{{Blockchain}}'),gas_price AS (SELECT date_trunc('hour', block_time) AS time,approx_percentile(gas_price, 0.5)/POWER(10, 9) AS median_gas,21000 * approx_percentile(gas_price, 0.5)/POWER(10, 18) AS median_eth_transfer_gasFROM evms.transactions txsINNER JOIN blockchains_filter bf ON bf.blockchain = txs.blockchainWHERE block_time > NOW() - interval '5' yearAND block_time < date_trunc('hour', NOW())GROUP BY date_trunc('hour', block_time))SELECT gp.time,gp.median_gas,gp.median_eth_transfer_gas,gp.median_eth_transfer_gas * MAX(pu.price) AS median_eth_transfer_priceFROM gas_price gpINNER JOIN prices.usd pu ON pu.minute >= gp.timeAND pu.minute < gp.time + interval '1' hourAND pu.symbol = 'ETH'GROUP BY gp.time, gp.median_gas, gp.median_eth_transfer_gas;