Query editor
-- 跨链价差第 1 步(源码见仓库 config/dune/xchain-candidates.sql)WITH params AS (SELECTCAST('{{start_date}}' AS DATE) AS start_d,CAST('{{end_date}}' AS DATE) AS end_d,COALESCE(TRY_CAST('{{min_rh_volume}}' AS DOUBLE), 20000e0) AS min_rh,COALESCE(TRY_CAST('{{min_other_volume}}' AS DOUBLE), 50000e0) AS min_other),vol AS (SELECT v.blockchain, v.token_address, MAX(v.symbol) AS symbol,SUM(v.volume_usd) AS volume_usd, SUM(v.volume) AS volumeFROM dex.token_volumes_daily vCROSS JOIN params pWHERE v.block_month >= CAST(DATE_TRUNC('month', p.start_d) AS DATE)AND v.block_date >= p.start_d AND v.block_date < p.end_dAND v.volume_usd IS NOT NULL AND v.symbol IS NOT NULLGROUP BY 1, 2),rh AS (SELECT v.*, UPPER(v.symbol) AS keyFROM vol v CROSS JOIN params pWHERE v.blockchain = 'robinhood' AND v.volume_usd >= p.min_rh),alias (rh_key, other_key) AS (VALUES ('HBTC', 'WBTC'), ('HBTC', 'CBBTC'), ('HBTC', 'BTCB'), ('HBTC', 'HBTC'),('HETH', 'WETH'), ('HETH', 'ETH'), ('WETH', 'ETH'),('USDG', 'USDC'), ('USDG', 'USDT')),keys AS (SELECT r.token_address AS rh_token, r.key AS rh_key, r.key AS other_key FROM rh rUNIONSELECT r.token_address, r.key, a.other_key FROM rh r JOIN alias a ON a.rh_key = r.key),other AS (SELECT k.rh_token, v.blockchain, v.token_address, v.symbol, v.volume_usd, v.volume,ROW_NUMBER() OVER (PARTITION BY k.rh_token ORDER BY v.volume_usd DESC) AS rnFROM keys kJOIN vol v ON UPPER(v.symbol) = k.other_key AND v.blockchain <> 'robinhood'CROSS JOIN params pWHERE v.volume_usd >= p.min_other)SELECTr.symbol AS "Robinhood币",CONCAT('0x', LOWER(TO_HEX(r.token_address))) AS "Robinhood合约",ROUND(r.volume_usd, 0) AS "Robinhood7天成交额$",r.volume_usd / NULLIF(r.volume, 0e0) AS "Robinhood均价$",o.blockchain AS "其他链",o.symbol AS "其他链符号",CONCAT('0x', LOWER(TO_HEX(o.token_address))) AS "其他链合约",ROUND(o.volume_usd, 0) AS "其他链7天成交额$",o.volume_usd / NULLIF(o.volume, 0e0) AS "其他链均价$",ROUND((r.volume_usd / NULLIF(r.volume, 0e0)) / NULLIF(o.volume_usd / NULLIF(o.volume, 0e0), 0e0) - 1e0, 4) AS "均价差(Robinhood比其他链)"FROM rh rJOIN other o ON o.rh_token = r.token_address AND o.rn <= 5ORDER BY r.volume_usd DESC, o.volume_usd DESC