Query editor
WITH ethereum AS (SELECT DATE_TRUNC('month', block_time) AS txn_date, blockchain, COUNT(transactions) AS txn_count, CAST(SUM(CASE WHEN status = TRUE THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(transactions), 0) * 100 AS success_txn_pct, CAST(SUM(CASE WHEN status = FALSE THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(transactions), 0) * 100 AS failed_txn_pct, APPROX_PERCENTILE(gas_fees, 0.5) as fees_ethFROM(SELECT txn.block_time, 'Ethereum Mainnet' AS blockchain, txn.hash AS transactions, CAST(txn.gas_price AS DOUBLE) * CAST(txn.gas_used AS DOUBLE) / 1e18 AS gas_fees, txn.success AS statusFROM ethereum.transactions txnWHERE txn.block_date >= TIMESTAMP '2023-09-01'AND txn.block_date < TIMESTAMP '2024-10-01')GROUP BY 1, 2),linea AS (SELECT DATE_TRUNC('month', block_time) AS txn_date, blockchain, COUNT(transactions) AS txn_count, CAST(SUM(CASE WHEN status = TRUE THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(transactions), 0) * 100 AS success_txn_pct, CAST(SUM(CASE WHEN status = FALSE THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(transactions), 0) * 100 AS failed_txn_pct, APPROX_PERCENTILE(gas_fees, 0.5) as fees_ethFROM(SELECT txn.block_time, 'Linea' AS blockchain, txn.hash AS transactions, CAST(txn.gas_price * txn.gas_used AS DOUBLE) / 1e18 AS gas_fees, txn.success AS statusFROM linea.transactions txnWHERE txn.block_date >= TIMESTAMP '2023-09-01'AND txn.block_date < TIMESTAMP '2024-10-01')GROUP BY 1, 2),scroll AS (SELECT DATE_TRUNC('month', block_time) AS txn_date, blockchain, COUNT(transactions) AS txn_count, CAST(SUM(CASE WHEN status = TRUE THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(transactions), 0) * 100 AS success_txn_pct, CAST(SUM(CASE WHEN status = FALSE THEN 1 ELSE 0 END) AS DOUBLE) / NULLIF(COUNT(transactions), 0) * 100 AS failed_txn_pct, APPROX_PERCENTILE(gas_fees, 0.5) as fees_ethFROM(SELECT txn.block_time, 'Scroll' AS blockchain, txn.hash AS transactions, CAST((txn.gas_price * txn.gas_used + txn.l1_fee) AS DOUBLE) / 1e18 AS gas_fees, txn.success AS statusFROM scroll.transactions txnWHERE txn.block_date >= TIMESTAMP '2023-09-01'AND txn.block_date < TIMESTAMP '2024-10-01')GROUP BY 1, 2),starknet AS (SELECT DATE_TRUNC('month', block_time) AS txn_date, blockchain