Query editor
-- 跨链搬砖机会扫描(源码见仓库 config/dune/bridge-flip.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('{{max_min}}' AS INTEGER), 10) AS max_min,COALESCE(TRY_CAST('{{min_usd}}' AS DOUBLE), 100e0) AS min_usd),tf AS (SELECT tr.block_time, tr.tx_hash, tr.tx_to, tr.contract_address AS token, tr."from" AS f, tr."to" AS t,COALESCE(tr.amount, CAST(tr.amount_raw AS DOUBLE) / 1e18) AS amtFROM tokens.transfers trCROSS JOIN params pWHERE tr.blockchain = 'robinhood' AND tr.token_standard = 'erc20'AND tr.contract_address <> 0x0bd7d308f8e1639fab988df18a8011f41eacad73AND tr.block_month >= CAST(DATE_TRUNC('month', p.start_ts) AS DATE)AND tr.block_time >= p.start_ts - INTERVAL '15' MINUTE AND tr.block_time < p.end_ts + INTERVAL '15' MINUTEAND (tr."from" = 0x0000000000000000000000000000000000000000OR tr."to" = 0x0000000000000000000000000000000000000000OR tr."to" = tr.contract_address)),mints AS (SELECT * FROM tf WHERE f = 0x0000000000000000000000000000000000000000),burns AS (SELECT * FROM tf WHERE t IN (0x0000000000000000000000000000000000000000, token)),dx AS (SELECT d.tx_from, d.tx_hash, d.block_time, d.amount_usd, d.token_bought_address AS tb, d.token_sold_address AS ts,d.token_bought_symbol AS sb, d.token_sold_symbol AS ssFROM 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 - INTERVAL '15' MINUTE AND d.block_time < p.end_ts + INTERVAL '15' MINUTEAND d.amount_usd >= p.min_usd),flip_in AS (SELECT m.token, m.t AS addr, m.block_time AS mint_time, m.tx_to AS bridge, m.amt,s.block_time AS sell_time, s.amount_usd AS usd, s.ss AS sym, s.tx_hash AS sell_tx, m.tx_hash AS bridge_tx,ROW_NUMBER() OVER (PARTITION BY m.tx_hash, m.t ORDER BY s.block_time) AS rnFROM mints mJOIN dx s ON s.tx_from = m.t AND s.ts = m.tokenCROSS JOIN params pWHERE m.block_time >= p.start_ts AND m.block_time < p.end_tsAND s.block_time BETWEEN m.block_time AND m.block_time + p.max_min * INTERVAL '1' MINUTE),flip_out AS (SELECT b.token, b.f AS addr, b.block_time AS burn_time, b.tx_to AS bridge, b.amt,x.block_time AS buy_time, x.amount_usd AS usd, x.sb AS sym, x.tx_hash AS buy_tx, b.tx_hash AS bridge_tx,ROW_NUMBER() OVER (PARTITION BY b.tx_hash, b.f ORDER BY x.block_time DESC) AS rnFROM burns bJOIN dx x ON x.tx_from = b.f AND x.tb = b.tokenCROSS JOIN params pWHERE b.block_time >= p.start_ts AND b.block_time < p.end_tsAND x.block_time BETWEEN b.block_time - p.max_min * INTERVAL '1' MINUTE AND b.block_time),ev AS (SELECT token, sym, 'in' AS dir, addr, bridge, usd, DATE_DIFF('second', mint_time, sell_time) AS secs, mint_time AS t, sell_tx AS tx, bridge_txFROM flip_in WHERE rn = 1UNION ALLSELECT token, sym, 'out' AS dir, addr, bridge, usd, DATE_DIFF('second', buy_time, burn_time) AS secs, burn_time AS t, buy_tx AS tx, bridge_txFROM flip_out WHERE rn = 1)