Query editor
WITH-- 协议地址映射表(转换为二进制格式以提高匹配效率)protocol_mapping AS (SELECTLOWER(address) as address_str,FROM_HEX(address) as address_binary,protocol_nameFROM (VALUES('Bb2CD025c6F81142F2c5300960865344f9eaB831', 'fluid lite'),('62a7b6eb6A5d2DcAa05Bf53C7272afd9dA460a2c', 'Hashflow PMM'),('69A52A0570636ca391175cE619Bbb9D348040Bbc', 'Dynamic Route'),('22ecf2Bed494976503A8087aDd30E134C30FBD98', 'Dynamic Route')) AS t(address, protocol_name)),-- 优化:添加时间范围限制,减少日志扫描量okx_eth_tx AS (SELECTcontract_address,tx_hash,block_timeFROM ethereum.logsWHERE 1=1AND block_time >= CURRENT_DATE - INTERVAL '90' DAYAND contract_address IN (0x3b3ae790Df4F312e745D270119c6052904FB6790,0x7D0CcAa3Fac1e5A943c5168b6CEd828691b46B36,0x1ef032a3c471a99cc31578c8007f256d95e89896,0xf73bd29daf60dfe09608d46f5e8eae87284fd3d3,0xf3de3c0d654fda23dad170f0f320a92172509127,0x1BDB7aB4C359769fDFE83d8813d61942f9DE8f01,0x156ACd2bc5fC336D59BAAE602a2BD9b5e20D6672,0x6088d94C5a40CEcd3ae2D4e0710cA687b91c61d0,0xDd5E9B947c99Aa60bab00ca4631Dce63b49983E7,0x2E1Dee213BA8d7af0934C49a23187BabEACa8764,0xF6801D319497789f934ec7F83E142a9536312B08,0x5E1f62Dac767b0491e3CE72469C217365D5B48cC,0x28b1Dc1a5E3699A428BC51d234DFab7C9CB2a183)AND topic0 = 0x1bb43f2da90e35f7b0cf38521ca95a49e68eb42fac49924930a5bd73cdf7576c),-- 优化:预先获取已知的dex交易hash,避免重复扫描existing_dex_hashes AS (SELECT DISTINCTa.tx_hash,a.projectFROM dex.trades AS aINNER JOIN okx_eth_tx AS b ON a.tx_hash = b.tx_hashWHERE a.blockchain = 'ethereum'AND a.block_time >= CURRENT_DATE - INTERVAL '90' DAY),-- 获取dex.trades中的数据,修正窗口函数逻辑dex_trades_with_first AS (SELECTa.block_time,CASEWHEN a.project = 'uniswap' THEN a.project || '_v' || a.version