Query editor
WITH max_bt AS (SELECT MAX(block_time) bt FROM optimism.transactions WHERE block_time > NOW() - interval '7' day), get_bots AS (SELECTaddress AS contract, nameFROM labels.likely_bot_contracts lINNER JOIN contracts.contract_mapping cmON cm.contract_address = l.addressAND contract_project IS NULLAND is_self_destruct = falseand cm.blockchain = 'optimism'WHERE category = 'likely bots'GROUP BY 1,2), get_daily_data AS (SELECTcontract_type, COALESCE( contract_name, token_symbol, '- unmapped -') AS contract_name,CASE WHEN '{{Granularity}}' = 'Method' THEN --segment to methodcoalesce(op.sig_map,l1.sig_map,cast( method as varchar))ELSE NULL --don'tEND AS method,trailing_day, --CEIL(hour_rank/24) AS trailing_day,cast( SUM(num_txs) as double) AS num_transactions,cast( COUNT(DISTINCT from_addr) as double) AS num_addresses,-- SUM(calldata) AS calldata,-- SUM(gas_used) AS gas_used,cast( SUM(eth_fees) as double) AS eth_fees,COALESCE( cast( SUM(eth_fees) as double) / cast( COUNT(DISTINCT from_addr) as double) ,0.0) AS eth_fees_per_addr,cast( SUM(l2_gas_used) as double) AS l2_gas_used,cast( SUM(l1_gas_used_uncompressed) as double) AS l1_gas_used_uncompressed,ARRAY_AGG(DISTINCT to_address) AS contract_addresses,concat_ws(',',ARRAY_AGG(DISTINCT bot_type)) AS bot_typesFROM(SELECTDATE_DIFF('second', DATE_TRUNC('hour',t.block_time), DATE_TRUNC('hour',max_bt.bt))/(60.0*60.0*24.0) AS trailing_day, "from" AS from_addr, t.to AS to_address, gb.name as bot_type-- CASE WHEN get_tx_type_by_method(substring(data from 1 for 4)) IS NULL THEN 1 ELSE 0 END AS is_project,COALESCE(gc.contract_project,CASEWHEN gb.contract IS NOT NULL THEN 'Other Bots'ELSE NULLEND,token_symbol, 'Other') AS contract_type,contract_name, token_symbol,