Query editor
WITH pricet AS (SELECTtimestamp AS pdate,priceFROM prices.hourWHERE blockchain = 'ethereum'AND contract_address = 0x0000000000000000000000000000000000000000AND timestamp > DATE('2024-04-01'))SELECTDATE_TRUNC('week', block_time) AS week,COUNT(DISTINCT hash) AS txs,COUNT(DISTINCT hash) * 1.0 / 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) / 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 dat(block_time)) AS users_per_weekFROM bob.transactionsJOIN pricet ON DATE_TRUNC('hour', block_time) = pdateGROUP BY 1ORDER BY 1;