Query editor
-- 我们监控的 9 个 OFT 币:每天有多少跨链价差机会(2026-10-09,lz-oft-gap.sql 的按天版,换成监控的链对)-- 每分钟成交均价(成交额 / 数量,只用 ≥ $20 的成交),两边同一分钟都有成交才比;价差 = Robinhood 均价 / 对端均价 − 1。-- 一段机会 = 连续(相邻分钟)价差绝对值 > 2% 的分钟。-- 注意:IMD 在 Base 的收税池里,dex.trades 的数量是扣税前的,所以 Base 均价偏低约 4%(Robinhood 看起来更贵),IMD 这一行的价差要减掉约 4% 再看。-- 一行 = 一个币 × 对端链 × 一天(UTC)。SQL 文件:oft-xchain-arb/docs/dune/oft-gap-daily.sql-- 参数(Text):start_date / end_date(UTC 日期,左闭右开),例如 2026-10-02 / 2026-10-09WITH 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),pairs (sym, rh_token, chain, o_token) AS (VALUES('EDEL', 0x88c4c42ed68584ff02f5a00d5d3d985108a3961c, 'base', 0xfb31f85a8367210b2e4ed2360d2da9dc2d2ccc95),('IMD', 0x5f7bb59365ce557c26dbcaa4ee9d39a4b95b7127, 'base', 0xff0c532fdb8cd566ae169c1cb157ff2bdc83e105),('Surplus', 0xa8219d665c831ab5caadb14b24de9605383f2119, 'base', 0xc52aedec3374422d7510e294cfaa90799595cba3),('BNKR', 0x178e54df3d091ee4d0b2534742ef9e3692b76526, 'base', 0x22af33fe49fd1fa80c7149773dde5890d3c76f3b),('MRDN', 0xbaabcb1800c539a58a46035d3982403a37fd6d5d, 'base', 0xe57e601c06689d3e2bf7db7bebb14b4ff28400c6),('Anon', 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c, 'base', 0x79bbf4508b1391af3a0f4b30bb5fc4aa9ab0e07c),('RAM', 0x5173d45a1191ee33cbb7d8c7e65f21b04ed54802, 'hyperevm', 0x555570a286f15ebdfe42b66ede2f724aa1ab5555),('PENGU', 0x74be72affafbc8de30f0c11247814036314d625f, 'bnb', 0x6418c0dd099a9fda397c766304cdd918233e8847),('ARENA', 0x50832d74a7160e2f7d361f5e678e107d228b9aa6, 'avalanche_c', 0xb8d7710f7d8349a506b75dd184f05777c82dad0c)),toks AS (SELECT DISTINCT 'robinhood' AS chain, rh_token AS token FROM pairsUNIONSELECT DISTINCT chain, o_token FROM pairs),tr AS (SELECT d.blockchain AS chain, x.token, DATE_TRUNC('minute', d.block_time) AS m, d.amount_usd AS usd, x.qtyFROM dex.trades dCROSS JOIN params pCROSS JOIN UNNEST(ARRAY[d.token_bought_address, d.token_sold_address], ARRAY[d.token_bought_amount, d.token_sold_amount]) AS x(token, qty)JOIN toks k ON k.chain = d.blockchain AND k.token = x.tokenWHERE d.blockchain IN ('robinhood', 'base', 'hyperevm', 'bnb', 'avalanche_c')AND d.block_month >= CAST(DATE_TRUNC('month', p.start_ts) AS DATE)AND d.block_time >= p.start_ts AND d.block_time < p.end_tsAND d.amount_usd >= 20e0 AND x.qty > 0e0),mins AS (SELECT chain, token, m, SUM(usd) / SUM(qty) AS px, SUM(usd) AS volFROM tr GROUP BY 1, 2, 3),day_vol AS (SELECT chain, token, DATE_TRUNC('day', m) AS d, SUM(vol) AS vol FROM mins GROUP BY 1, 2, 3),gaps AS (SELECT pr.sym, pr.chain, pr.rh_token, pr.o_token, r.m, r.px / o.px - 1e0 AS gap, LEAST(r.vol, o.vol) AS small_volFROM pairs prJOIN mins r ON r.chain = 'robinhood' AND r.token = pr.rh_tokenJOIN mins o ON o.chain = pr.chain AND o.token = pr.o_token AND o.m = r.m),seg AS (SELECT g.*,(ABS(g.gap) > 0.02e0) AS big,LAG(g.m) OVER (PARTITION BY g.sym, g.chain ORDER BY g.m) AS prev_m,LAG(ABS(g.gap) > 0.02e0) OVER (PARTITION BY g.sym, g.chain ORDER BY g.m) AS prev_bigFROM gaps g),seg2 AS (SELECT s.*, DATE_TRUNC('day', s.m) AS d,(s.big AND NOT (COALESCE(s.prev_big, false) AND s.prev_m = s.m - INTERVAL '1' MINUTE)) AS seg_start