Query editor
WITH pricet AS (SELECTtimestamp AS pdate,priceFROM prices.hourWHERE blockchain = 'ethereum'AND contract_address = 0x0000000000000000000000000000000000000000AND timestamp > DATE('2024-04-01'))SELECTMin(block_time) as inception,COUNT(DISTINCT hash) AS txs,COUNT(DISTINCT hash) * COUNT(DISTINCT DATE_TRUNC('week', block_time)) / 604800 AS avg_tps,SUM(((gas_price * gas_used + l1_fee) / 1e18) * price) AS fee,AVG(((gas_price * gas_used + l1_fee) / 1e18) * price) AS avg_fee,COUNT(DISTINCT "from") AS users,COUNT(DISTINCT CASE WHEN success = true THEN hash END) AS succeeded_txs,COUNT(DISTINCT CASE WHEN success = false THEN hash END) AS failed_txs,CAST(COUNT(DISTINCT CASE WHEN success = true THEN hash END) AS DOUBLE) * 100 /(COUNT(DISTINCT CASE WHEN success = true THEN hash END) + COUNT(DISTINCT CASE WHEN success = false THEN hash END)) AS success_rate,COUNT(DISTINCT hash) * 1.0 / COUNT(DISTINCT "from") AS tx_per_user,COUNT(DISTINCT hash) * 1.0 / COUNT(DISTINCT DATE(block_time)) AS txs_per_week,COUNT(DISTINCT "from") * 1.0 / COUNT(DISTINCT DATE(block_time)) AS users_per_weekFROM bob.transactionsJOIN pricet ON DATE_TRUNC('hour', block_time) = pdate