Query editor
-- 跨链价差第 2 步:按地址找跨链搬砖的人(源码见仓库 config/dune/xchain-same-address.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_gap_min}}' AS INTEGER), 10) AS max_gap_min,COALESCE(TRY_CAST('{{min_usd}}' AS DOUBLE), 100e0) AS min_usd),raw AS (SELECT d.blockchain, d.tx_from, d.tx_hash, d.block_time, d.amount_usd,UPPER(d.token_bought_symbol) AS sb, UPPER(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 IN ('robinhood', 'ethereum', 'base', 'arbitrum', 'bnb', 'optimism', 'polygon', 'unichain')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 >= p.min_usd),rh_addr AS (SELECT DISTINCT tx_from FROM raw WHERE blockchain = 'robinhood'),norm AS (SELECT r.*,CASE WHEN r.sb IN ('WETH', 'ETH') THEN 'ETH' WHEN r.sb IN ('HBTC', 'WBTC', 'CBBTC', 'BTCB') THEN 'BTC'WHEN r.sb IN ('USDG', 'USDC', 'USDT', 'USDC.E', 'USDBC', 'USDT0') THEN '$' ELSE r.sb END AS nb,CASE WHEN r.ss IN ('WETH', 'ETH') THEN 'ETH' WHEN r.ss IN ('HBTC', 'WBTC', 'CBBTC', 'BTCB') THEN 'BTC'WHEN r.ss IN ('USDG', 'USDC', 'USDT', 'USDC.E', 'USDBC', 'USDT0') THEN '$' ELSE r.ss END AS nsFROM raw rJOIN rh_addr a ON a.tx_from = r.tx_from),tr AS (SELECT blockchain, tx_from, tx_hash, block_time, amount_usd,CASE WHEN nb <> '$' AND (ns = '$' OR nb <> 'ETH') THEN nb ELSE ns END AS asset,CASE WHEN nb <> '$' AND (ns = '$' OR nb <> 'ETH') THEN 1 ELSE -1 END AS dir,CASE WHEN nb <> '$' AND (ns = '$' OR nb <> 'ETH') THEN ab ELSE asold END AS qtyFROM normWHERE NOT (nb = '$' AND ns = '$') AND nb IS NOT NULL AND ns IS NOT NULL),tr2 AS (SELECT t.*, t.amount_usd / NULLIF(t.qty, 0e0) AS px FROM tr t WHERE t.qty > 0e0),pairs AS (SELECT r.tx_from, r.asset, r.tx_hash AS rh_tx, r.block_time AS rh_time, r.dir AS rh_dir, r.amount_usd AS rh_usd, r.px AS rh_px, r.qty AS rh_qty,o.blockchain AS o_chain, o.tx_hash AS o_tx, o.block_time AS o_time, o.amount_usd AS o_usd, o.px AS o_px, o.qty AS o_qty,ROW_NUMBER() OVER (PARTITION BY r.tx_hash, r.asset ORDER BY ABS(DATE_DIFF('second', r.block_time, o.block_time))) AS rnFROM tr2 rJOIN tr2 o ON o.tx_from = r.tx_from AND o.asset = r.asset AND o.dir = -r.dir AND o.blockchain <> 'robinhood'CROSS JOIN params pWHERE r.blockchain = 'robinhood'AND o.block_time BETWEEN r.block_time - p.max_gap_min * INTERVAL '1' MINUTE AND r.block_time + p.max_gap_min * INTERVAL '1' MINUTEAND o.amount_usd BETWEEN 0.5e0 * r.amount_usd AND 2e0 * r.amount_usdAND ABS(r.px / NULLIF(o.px, 0e0) - 1e0) < 0.3e0),p1 AS (SELECT x.*,LEAST(x.rh_qty, x.o_qty) * IF(x.rh_dir = -1, x.rh_px - x.o_px, x.o_px - x.rh_px) AS edge,IF(x.rh_dir = -1, 'Robinhood卖', 'Robinhood买') AS sideFROM pairs xWHERE x.rn = 1