Query editor
-- OKX EVM 单日 flow-v3 汇总。-- 业务口径(2026-07-29):10 位 = API;17 位 = Interface;其余含 NULL = Bot。-- 复用 query_6258999 的十一链路由日志识别及 dex.trades USD 计量。WITH decoded AS (SELECTCAST(block_date AS DATE) AS block_date,blockchain,tx_hash,"user",usd_value,order_id,CASEWHEN order_id IS NULL THEN NULLELSE LENGTH(CAST(varbinary_to_uint256(varbinary_substring(order_id, 1, 32))AS VARCHAR))END AS order_id_lengthFROM "query_6258999(dt='{{dt}}')"WHERE CAST(block_date AS DATE) = DATE('{{dt}}')-- 与现有 6970514 的 EVM 防异常口径一致;保留未定价行用于覆盖率检查。AND (usd_value IS NULL OR (usd_value >= 0 AND usd_value < 10000000))),classified AS (SELECT*,CASEWHEN order_id_length = 10 THEN 'api'WHEN order_id_length = 17 THEN 'interface'ELSE 'okx_bot'END AS flowFROM decoded)SELECTDATE('{{dt}}') AS block_date,COUNT(*) AS total_pool_calls,COUNT_IF(flow = 'okx_bot') AS bot_pool_calls,COUNT_IF(usd_value IS NOT NULL) AS valued_pool_calls,COUNT_IF(usd_value IS NULL) AS unvalued_pool_calls,SUM(usd_value) AS total_volume_usd,SUM(CASE WHEN flow = 'okx_bot' THEN usd_value ELSE 0 END) AS bot_volume_usd,100.0 * SUM(CASE WHEN flow = 'okx_bot' THEN usd_value ELSE 0 END)/ NULLIF(SUM(usd_value), 0) AS bot_volume_share_pct,COUNT(DISTINCT CONCAT(blockchain, ':', CAST(tx_hash AS VARCHAR))) AS total_transactions,COUNT(DISTINCT CASE WHEN flow = 'okx_bot'THEN CONCAT(blockchain, ':', CAST(tx_hash AS VARCHAR)) END) AS bot_transactions,COUNT(DISTINCT CAST("user" AS VARCHAR)) AS total_unique_addresses,COUNT(DISTINCT CASE WHEN flow = 'okx_bot'THEN CAST("user" AS VARCHAR) END) AS bot_unique_addressesFROM classified;