Query editor
WITH zkp_efficiency AS (SELECTDATE(block_time) as date,AVG(gas_price) as avg_zkp_gas_price,MIN(gas_price) as min_zkp_gas_price,MAX(gas_price) as max_zkp_gas_price,COUNT(*) as zkp_activity,COUNT(DISTINCT "from") as unique_users,SUM(CAST(value AS DECIMAL(38,0)) / 1e18) as total_volumeFROM zksync.transactionsWHERE block_time >= CURRENT_DATE - INTERVAL '7' DAYAND CAST(value AS DECIMAL(38,0)) / 1e18 > 100AND gas_used > 50000GROUP BY DATE(block_time)),rwa_token_activity AS (SELECTDATE(block_time) as date,COUNT(*) as rwa_transfers,COUNT(DISTINCT contract_address) as unique_rwa_tokens,AVG(CAST(amount AS DECIMAL(38,0)) / 1e18) as avg_transfer_sizeFROM 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%'OR LOWER(name) LIKE '%tokenized%')GROUP BY DATE(block_time))SELECTz.date,z.avg_zkp_gas_price,z.zkp_activity,z.unique_users,z.total_volume,r.rwa_transfers,r.unique_rwa_tokens,r.avg_transfer_size,CASEWHEN z.avg_zkp_gas_price < 1000000000 AND z.zkp_activity > 100 THEN 'HIGH_EFFICIENCY'WHEN z.avg_zkp_gas_price < 5000000000 AND z.zkp_activity > 50 THEN 'MEDIUM_EFFICIENCY'ELSE 'LOW_EFFICIENCY'END as efficiency_level,CASEWHEN z.avg_zkp_gas_price < 2000000000 AND r.rwa_transfers > 50 THEN 'STRONG_CORRELATION'WHEN z.avg_zkp_gas_price < 5000000000 AND r.rwa_transfers > 20 THEN 'MEDIUM_CORRELATION'ELSE 'WEAK_CORRELATION'END as price_correlation_signalFROM zkp_efficiency zLEFT JOIN rwa_token_activity r ON z.date = r.dateORDER BY efficiency_level, z.date DESC;