Query editor
WITH bridge_contracts AS (SELECT addressFROM ethereum.contractsWHERE LOWER(name) LIKE '%bridge%'OR LOWER(name) LIKE '%cross%'OR LOWER(name) LIKE '%wormhole%'OR LOWER(name) LIKE '%stargate%'OR LOWER(name) LIKE '%multichain%'OR LOWER(name) LIKE '%synapse%'OR LOWER(name) LIKE '%layerzero%'),cross_chain_patterns AS (SELECTDATE(block_time) as date,COUNT(*) as cross_chain_txs,COUNT(DISTINCT "from") as unique_bridgers,COUNT(DISTINCT "to") as unique_destinations,SUM(CAST(value AS DECIMAL(38,0)) / 1e18) as total_volume,AVG(CAST(value AS DECIMAL(38,0)) / 1e18) as avg_bridge_size,AVG(gas_used) as avg_gas_used,AVG(gas_price) as avg_gas_priceFROM ethereum.transactionsWHERE block_time >= CURRENT_DATE - INTERVAL '7' DAYAND CAST(value AS DECIMAL(38,0)) / 1e18 > 1000AND gas_used > 200000AND "to" IN (SELECT address FROM bridge_contracts)GROUP BY DATE(block_time))SELECTdate,cross_chain_txs,unique_bridgers,unique_destinations,total_volume,avg_bridge_size,avg_gas_used,avg_gas_price,LAG(cross_chain_txs) OVER (ORDER BY date) as prev_day_txs,(cross_chain_txs - LAG(cross_chain_txs) OVER (ORDER BY date)) as daily_increase,CASEWHEN (cross_chain_txs - LAG(cross_chain_txs) OVER (ORDER BY date)) > 20 THEN 'HIGH_GROWTH'WHEN (cross_chain_txs - LAG(cross_chain_txs) OVER (ORDER BY date)) > 5 THEN 'MEDIUM_GROWTH'WHEN (cross_chain_txs - LAG(cross_chain_txs) OVER (ORDER BY date)) > 0 THEN 'STABLE_GROWTH'ELSE 'DECLINING'END as growth_signalFROM cross_chain_patternsORDER BY daily_increase DESC;