Query editor
WITH celo_price AS (SELECTdate_trunc('day', minute) AS date,AVG(price) AS priceFROM prices.usdWHERE symbol = 'CELO'GROUP BY 1),transactions AS (SELECTt.date,SUM(total_transactions) OVER (ORDER BY t.date) AS total_transactions,SUM(transaction_fee_in_celo) OVER (ORDER BY t.date) AS transaction_fee_in_celo,SUM(transaction_fee_in_celo * price) OVER (ORDER BY t.date) AS transaction_fee_in_usdFROM (SELECTdate_trunc('day', block_time) AS date,COUNT(hash) AS total_transactions,SUM(CAST(gas_used AS DOUBLE) * gas_price) / 1e18 AS transaction_fee_in_celoFROM celo.transactionsGROUP BY 1) tLEFT JOIN celo_price pON t.date = p.dateORDER BY 1 DESCLIMIT 1),block_time AS (SELECTtime - LAG(time, 1) OVER (ORDER BY time) AS time_diffFROM celo.blocksWHERE time IS NOT NULLAND date >= DATE('2025-03-26') --checking since L2 launch),block_time_agg AS (SELECTSECOND(AVG(time_diff)) AS average_block_timeFROM block_time),block_stats AS (SELECTCOUNT(*) / 1e6 AS total_blocks,MIN(time) AS min_block_time,COUNT(*) / ((to_unixtime(now()) - to_unixtime(MIN(time))) / 60) AS avg_block_per_min,SUM(gas_used) / 1e9 AS total_gas_usedFROM celo.blocks)SELECTaverage_block_time,total_blocks,min_block_time,avg_block_per_min,total_gas_used,total_transactions / 1e9 AS total_transactions,transaction_fee_in_celo,transaction_fee_in_celo / total_transactions AS avg_transaction_fee_in_celo,transaction_fee_in_usd,transaction_fee_in_usd / total_transactions AS avg_transaction_fee_in_usdFROM block_statsJOIN block_time_agg ON TRUE