Query editor
-- oft-flips 的按天版(2026-10-09):每个币 × 方向 × 天,看利润是天天有还是集中在某一两天。买卖配对逻辑和 oft-flips.sql(Dune 8919287)完全一样。-- 注意:只能算出卖出能对上的那部分(例如 0x6338 合约 R→B 由另一个钱包在 Base 卖,这里对不上,不计利润)。-- 参数(Text):start_date / end_date(UTC 日期,左闭右开)WITH params AS (SELECT CAST(CAST('{{start_date}}' AS DATE) AS TIMESTAMP) AS start_ts, CAST(CAST('{{end_date}}' AS DATE) AS TIMESTAMP) AS end_ts),toks (sym, chain, eid, token, oft) AS (VALUES('EDEL', 'base', 30184, 0xfb31f85a8367210b2e4ed2360d2da9dc2d2ccc95, 0x88c4c42ed68584ff02f5a00d5d3d985108a3961c),('EDEL', 'robinhood', 30416, 0x88c4c42ed68584ff02f5a00d5d3d985108a3961c, 0x88c4c42ed68584ff02f5a00d5d3d985108a3961c),('IMD', 'base', 30184, 0xff0c532fdb8cd566ae169c1cb157ff2bdc83e105, 0xab152db8aac047b6757ffcf495ffe88d7712690a),('IMD', 'robinhood', 30416, 0x5f7bb59365ce557c26dbcaa4ee9d39a4b95b7127, 0x5f7bb59365ce557c26dbcaa4ee9d39a4b95b7127),('Surplus', 'base', 30184, 0xc52aedec3374422d7510e294cfaa90799595cba3, 0x12cf078d3d4808dbdc69701f30b0b409aff89ec1),('Surplus', 'robinhood', 30416, 0xa8219d665c831ab5caadb14b24de9605383f2119, 0xa8219d665c831ab5caadb14b24de9605383f2119),('BNKR', 'base', 30184, 0x22af33fe49fd1fa80c7149773dde5890d3c76f3b, 0xe4bbb2d00aac984ccca26ac5abafd7de3afab8bf),('BNKR', 'robinhood', 30416, 0x178e54df3d091ee4d0b2534742ef9e3692b76526, 0x178e54df3d091ee4d0b2534742ef9e3692b76526),('MRDN', 'base', 30184, 0xe57e601c06689d3e2bf7db7bebb14b4ff28400c6, 0x6dd74a4b532659ce2f82e85cccd2e5989cea3589),('MRDN', 'robinhood', 30416, 0xbaabcb1800c539a58a46035d3982403a37fd6d5d, 0xbaabcb1800c539a58a46035d3982403a37fd6d5d),('Anon', 'base', 30184, 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c, 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c),('Anon', 'robinhood', 30416, 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c, 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c),('RAM', 'hyperevm', 30367, 0x555570a286f15ebdfe42b66ede2f724aa1ab5555, 0xe1c39b767e968a57af2ecad2f5b09ee2e7874d9f),('RAM', 'robinhood', 30416, 0x5173d45a1191ee33cbb7d8c7e65f21b04ed54802, 0x5173d45a1191ee33cbb7d8c7e65f21b04ed54802),('PENGU', 'bnb', 30102, 0x6418c0dd099a9fda397c766304cdd918233e8847, 0x6418c0dd099a9fda397c766304cdd918233e8847),('PENGU', 'robinhood', 30416, 0x74be72affafbc8de30f0c11247814036314d625f, 0x74be72affafbc8de30f0c11247814036314d625f),('ARENA', 'avalanche_c', 30106, 0xb8d7710f7d8349a506b75dd184f05777c82dad0c, 0xa59ad32dad425250ca3601f964d92611818f86f7),('ARENA', 'robinhood', 30416, 0x50832d74a7160e2f7d361f5e678e107d228b9aa6, 0x50832d74a7160e2f7d361f5e678e107d228b9aa6)),ev AS (SELECT 'robinhood' AS chain, l.* FROM robinhood.logs l CROSS JOIN params pWHERE l.block_date >= CAST(p.start_ts AS DATE) AND l.block_date <= CAST(p.end_ts AS DATE)AND l.topic0 IN (0x85496b760a4b7f8d66384b9df21b381f5d1b1e79f229a47aaf4c232edc2fe59a, 0xefed6d3500546b29533b128a29e3a94d70788727f0507505ac12eaf2e578fd9c)AND l.contract_address IN (SELECT oft FROM toks WHERE chain = 'robinhood')UNION ALLSELECT 'base', l.* FROM base.logs l CROSS JOIN params pWHERE l.block_date >= CAST(p.start_ts AS DATE) AND l.block_date <= CAST(p.end_ts AS DATE)AND l.topic0 IN (0x85496b760a4b7f8d66384b9df21b381f5d1b1e79f229a47aaf4c232edc2fe59a, 0xefed6d3500546b29533b128a29e3a94d70788727f0507505ac12eaf2e578fd9c)AND l.contract_address IN (SELECT oft FROM toks WHERE chain = 'base')UNION ALLSELECT 'hyperevm', l.* FROM hyperevm.logs l CROSS JOIN params pWHERE l.block_date >= CAST(p.start_ts AS DATE) AND l.block_date <= CAST(p.end_ts AS DATE)AND l.topic0 IN (0x85496b760a4b7f8d66384b9df21b381f5d1b1e79f229a47aaf4c232edc2fe59a, 0xefed6d3500546b29533b128a29e3a94d70788727f0507505ac12eaf2e578fd9c)AND l.contract_address IN (SELECT oft FROM toks WHERE chain = 'hyperevm')UNION ALLSELECT 'bnb', l.* FROM bnb.logs l CROSS JOIN params pWHERE l.block_date >= CAST(p.start_ts AS DATE) AND l.block_date <= CAST(p.end_ts AS DATE)AND l.topic0 IN (0x85496b760a4b7f8d66384b9df21b381f5d1b1e79f229a47aaf4c232edc2fe59a, 0xefed6d3500546b29533b128a29e3a94d70788727f0507505ac12eaf2e578fd9c)AND l.contract_address IN (SELECT oft FROM toks WHERE chain = 'bnb')UNION ALLSELECT 'avalanche_c', l.* FROM avalanche_c.logs l CROSS JOIN params pWHERE l.block_date >= CAST(p.start_ts AS DATE) AND l.block_date <= CAST(p.end_ts AS DATE)AND l.topic0 IN (0x85496b760a4b7f8d66384b9df21b381f5d1b1e79f229a47aaf4c232edc2fe59a, 0xefed6d3500546b29533b128a29e3a94d70788727f0507505ac12eaf2e578fd9c)AND l.contract_address IN (SELECT oft FROM toks WHERE chain = 'avalanche_c')),sent AS (SELECT t.sym, e.chain AS src, t.token AS src_token, e.topic1 AS guid, bytearray_substring(e.topic2, 13, 20) AS sender,e.tx_from, e.tx_hash, e.block_time AS sent_time,bytearray_to_bigint(bytearray_substring(e.data, 29, 4)) AS dst_eid,