Query editor
-- Robinhood 链原子套利排行(源码见仓库 config/dune/atomic-arb-leaderboard.sql)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.project, d.version, d.amount_usd, d.project_contract_address AS pool,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 NULLAND d.tx_to NOT IN (0x0000000071727de22e5e9d8baf0edac6f37da032, 0x5ff137d4b0fdcd49dca30c7cf57e578a026d2789)),legs AS (SELECT t.tx_from, t.tx_to, t.tx_hash, t.block_time, 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)),tx_asset AS (SELECT tx_from, 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 legsGROUP BY 1, 2, 3),tx_lvl AS (SELECT a.tx_from, a.tx_hash,SUM(a.cash) AS profit,COUNT_IF(NOT a.is_cash) AS toks,COUNT_IF(NOT a.is_cash AND ABS(a.qty) > 1e-6 * a.gross) AS toks_changed,ARRAY_JOIN(ARRAY_SORT(ARRAY_AGG(IF(NOT a.is_cash, COALESCE(a.sym, SUBSTR(TO_HEX(a.asset), 1, 8))))), '/') AS route_tokensFROM tx_asset aGROUP BY 1, 2),arb AS (SELECT l.*FROM tx_lvl lCROSS JOIN params pWHERE l.toks >= 1 AND l.toks_changed = 0 AND l.profit >= p.min_profit),arb_meta AS (SELECT t.tx_hash, MIN(t.block_time) AS bt, MIN(t.tx_to) AS tx_to, COUNT(*) AS legs,ARRAY_JOIN(ARRAY_SORT(ARRAY_DISTINCT(ARRAY_AGG(CONCAT(t.project, COALESCE(t.version, ''))))), '+') AS dexes,SUM(t.amount_usd) AS leg_usdFROM tJOIN arb a ON a.tx_hash = t.tx_hashGROUP BY 1),txs AS (SELECT tr.hash, tr."from" AS sender, tr.success,