Query editor
-- 跨链价差第 3 步:搬砖之后价差还剩多久(源码见仓库 config/dune/xchain-gap-window.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,COALESCE(TRY_CAST('{{min_edge}}' AS DOUBLE), 5e0) AS min_edge),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_address AS tb, d.token_sold_address AS ts,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 - INTERVAL '30' MINUTE AND d.block_time < p.end_ts + INTERVAL '40' MINUTEAND d.amount_usd >= 10e0),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 r),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 tb ELSE ts END AS token,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),rh_addr AS (SELECT DISTINCT t.tx_from FROM tr2 t CROSS JOIN params pWHERE t.blockchain = 'robinhood' AND t.amount_usd >= p.min_usd AND t.block_time >= p.start_ts AND t.block_time < p.end_ts),mine AS (SELECT t.* FROM tr2 t JOIN rh_addr a ON a.tx_from = t.tx_from CROSS JOIN params p WHERE t.amount_usd >= p.min_usd),pairs AS (SELECT r.tx_from, r.asset, r.token AS rh_token, 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.token AS o_token, 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 mine rJOIN mine 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 r.block_time >= p.start_ts AND r.block_time < p.end_tsAND 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