Query editor
WITH migration_signals AS (SELECTDATE(ethereum.block_time) as date,COUNT(*) as rwa_ethereum_txs,COUNT(zksync.block_time) as rwa_zksync_txs,SUM(CAST(ethereum.value AS DECIMAL(38,0)) / 1e18) as eth_volume,SUM(CAST(zksync.value AS DECIMAL(38,0)) / 1e18) as zksync_volume,(COUNT(zksync.block_time) / NULLIF(COUNT(*), 0)) * 100 as migration_ratioFROM ethereum.transactions ethereumLEFT JOIN zksync.transactions zksyncON DATE(ethereum.block_time) = DATE(zksync.block_time)AND CAST(ethereum.value AS DECIMAL(38,0)) / 1e18 > 1000AND CAST(zksync.value AS DECIMAL(38,0)) / 1e18 > 100WHERE ethereum.block_time >= CURRENT_DATE - INTERVAL '7' DAYAND CAST(ethereum.value AS DECIMAL(38,0)) / 1e18 > 1000GROUP BY DATE(ethereum.block_time)),efficiency_signals AS (SELECTDATE(block_time) as date,AVG(gas_price) as avg_zkp_gas_price,MIN(gas_price) as min_zkp_gas_price,COUNT(*) as zkp_activity,COUNT(DISTINCT "from") as unique_zkp_usersFROM zksync.transactionsWHERE block_time >= CURRENT_DATE - INTERVAL '7' DAYAND CAST(value AS DECIMAL(38,0)) / 1e18 > 100AND gas_used > 50000GROUP BY DATE(block_time)),volume_signals AS (SELECTDATE(block_time) as date,COUNT(*) as rwa_transfers,COUNT(DISTINCT contract_address) as unique_rwa_tokens,SUM(CAST(amount AS DECIMAL(38,0)) / 1e18) as total_rwa_volumeFROM tokens.transfersWHERE block_time >= CURRENT_DATE - INTERVAL '7' DAYAND contract_address IN (SELECT contract_addressFROM tokens.erc20WHERE LOWER(name) LIKE '%rwa%'OR LOWER(name) LIKE '%real%')GROUP BY DATE(block_time))SELECTm.date,m.migration_ratio,m.eth_volume,m.zksync_volume,e.avg_zkp_gas_price,e.zkp_activity,e.unique_zkp_users,v.rwa_transfers,v.unique_rwa_tokens,v.total_rwa_volume,-- 종합 투자 신호 점수 계산CASEWHEN m.migration_ratio > 30 AND e.avg_zkp_gas_price < 2000000000 AND v.rwa_transfers > 100 THEN 'VERY_STRONG_SIGNAL'