Query editor
-- Robinhood 链"慢一步"套利机会在哪(源码见仓库 config/dune/arb-slow-pools.sql;v2:v4 池子按 PoolManager + 两个币区分)WITH params AS (SELECTCAST(CAST('{{start_date}}' AS DATE) AS TIMESTAMP) AS start_ts,CAST(CAST('{{end_date}}' AS DATE) AS TIMESTAMP) AS end_ts,COALESCE(TRY_CAST('{{min_profit}}' AS DOUBLE), 0.1e0) AS min_profit),t AS (SELECTd.tx_from, d.tx_to, d.tx_hash, d.block_time, d.block_number, d.evt_index, d.amount_usd,CONCAT(d.project_contract_address,IF(d.token_bought_address < d.token_sold_address, d.token_bought_address, d.token_sold_address),IF(d.token_bought_address < d.token_sold_address, d.token_sold_address, d.token_bought_address)) AS pool,d.project_contract_address AS pool_contract,IF(d.token_bought_address < d.token_sold_address, CONCAT(d.token_bought_symbol, '-', d.token_sold_symbol),CONCAT(d.token_sold_symbol, '-', d.token_bought_symbol)) AS pair,CONCAT(d.project, COALESCE(d.version, '')) AS dex,IF(d.token_bought_address = 0x0000000000000000000000000000000000000000, 0x0bd7d308f8e1639fab988df18a8011f41eacad73, d.token_bought_address) AS tb,IF(d.token_sold_address = 0x0000000000000000000000000000000000000000, 0x0bd7d308f8e1639fab988df18a8011f41eacad73, d.token_sold_address) AS ts,d.token_bought_symbol AS sb, d.token_sold_symbol AS ss,d.token_bought_amount AS ab, d.token_sold_amount AS asoldFROM dex.trades dCROSS JOIN params pWHERE d.blockchain = 'robinhood'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 IS NOT NULL),t_prev AS (SELECT t.*,LAG(t.tx_hash) OVER (PARTITION BY t.pool ORDER BY t.block_number, t.evt_index) AS p_hash, LAG(t.block_number) OVER (PARTITION BY t.pool ORDER BY t.block_number, t.evt_index) AS p_block, LAG(t.evt_index) OVER (PARTITION BY t.pool ORDER BY t.block_number, t.evt_index) AS p_evt,LAG(t.amount_usd) OVER (PARTITION BY t.pool ORDER BY t.block_number, t.evt_index) AS p_usd, LAG(t.tx_from) OVER (PARTITION BY t.pool ORDER BY t.block_number, t.evt_index) AS p_from, LAG(t.block_time) OVER (PARTITION BY t.pool ORDER BY t.block_number, t.evt_index) AS p_timeFROM t),legs AS (SELECT t.tx_hash, t.tx_from, t.tx_to, x.asset, x.sym, x.qty,(x.asset IN (0x0bd7d308f8e1639fab988df18a8011f41eacad73, 0x5fc5360d0400a0fd4f2af552add042d716f1d168)) AS is_cash,x.sgn * t.amount_usd AS usdFROM tCROSS JOIN UNNEST(ARRAY[t.tb, t.ts], ARRAY[t.sb, t.ss], ARRAY[t.ab, -t.asold], ARRAY[1e0, -1e0]) AS x(asset, sym, qty, sgn)WHERE t.tx_to NOT IN (0x0000000071727de22e5e9d8baf0edac6f37da032, 0x5ff137d4b0fdcd49dca30c7cf57e578a026d2789)),tx_asset AS (SELECT tx_hash, asset, MAX(sym) AS sym, BOOL_OR(is_cash) AS is_cash, SUM(qty) AS qty, SUM(ABS(qty)) AS gross, SUM(IF(is_cash, usd, 0e0)) AS cashFROM legs GROUP BY 1, 2),arb AS (SELECT a.tx_hash, SUM(a.cash) AS profit,ARRAY_JOIN(ARRAY_SORT(ARRAY_AGG(IF(NOT a.is_cash, COALESCE(a.sym, SUBSTR(TO_HEX(a.asset), 1, 8))))), '/') AS routeFROM tx_asset aCROSS JOIN params pGROUP BY a.tx_hash, p.min_profitHAVING COUNT_IF(NOT a.is_cash) >= 1 AND COUNT_IF(NOT a.is_cash AND ABS(a.qty) > 1e-6 * a.gross) = 0 AND SUM(a.cash) >= p.min_profit),arb_trig AS (SELECT tp.tx_hash,MIN(tp.tx_to) AS contract, MIN(tp.tx_from) AS sender, MIN(tp.block_number) AS bn, MIN(tp.block_time) AS bt,COUNT(*) AS legs, COUNT(DISTINCT tp.pool) AS pools,ARRAY_JOIN(ARRAY_SORT(ARRAY_DISTINCT(ARRAY_AGG(tp.dex))), '+') AS dexes,ARRAY_JOIN(ARRAY_SORT(ARRAY_DISTINCT(ARRAY_AGG(CONCAT(tp.dex, ':', COALESCE(tp.pair, '?'), '@0x', LOWER(TO_HEX(tp.pool_contract)))))), ' ') AS pool_set,