Query editor
-- LayerZero 全链线路总览(2026-10-14):源码 oft-xchain-arb/docs/dune/lz-routes.sql,说明见文件头。"跨得最多的地址"输出完整地址。WITH params AS (SELECT CAST('{{start_time}}' AS TIMESTAMP) AS t0, CAST('{{end_time}}' AS TIMESTAMP) AS t1),base AS (SELECT m.primary_key, m.source_chain AS src, m.destination_chain AS dst, m.source_chain || '→' || m.destination_chain AS route,m.addr_from AS sender, m.ts_source, m.ts_destination, m.project, m.state, m.source_contract, m.destination_contract,t.symbol, t.type_transfer, COALESCE(t.usd_value, m.usd_value) AS usdFROM layerzero.messages mCROSS JOIN params pJOIN layerzero.transfers t ON t.messages_foreign_key = m.primary_keyWHERE m.ts_source >= p.t0 AND m.ts_source < p.t1 AND t.symbol IS NOT NULL),per_sender AS (SELECT b.symbol, b.route, b.src, b.dst, b.sender, COUNT(*) AS n, SUM(b.usd) AS usdFROM base b GROUP BY 1, 2, 3, 4, 5),rev AS (SELECT a.symbol, a.route, a.senderFROM per_sender a JOIN per_sender r ON r.symbol = a.symbol AND r.sender = a.sender AND r.src = a.dst AND r.dst = a.src),routes AS (SELECT b.symbol, b.route,ARBITRARY(b.project) AS project, ARBITRARY(b.type_transfer) AS type_transfer,COUNT(*) AS n, SUM(b.usd) AS usd, COUNT(DISTINCT b.sender) AS senders,APPROX_PERCENTILE(b.usd, 0.5) AS med_usd,APPROX_PERCENTILE(to_unixtime(b.ts_destination) - to_unixtime(b.ts_source), 0.5) AS med_sec,COUNT_IF(b.state <> 'Success') AS not_ok,ARBITRARY(b.source_contract) AS src_contract, ARBITRARY(b.destination_contract) AS dst_contractFROM base b GROUP BY 1, 2),senders AS (SELECT s.symbol, s.route,COUNT_IF(s.n >= 5) AS rep_senders,SUM(IF(s.n >= 5, s.usd, 0e0)) AS rep_usd,COUNT(r.sender) AS rev_senders,ARRAY_JOIN(SLICE(ARRAY_AGG(s.sender || ' ' || CAST(s.n AS VARCHAR) || '次 $' || CAST(CAST(ROUND(COALESCE(s.usd, 0e0), 0) AS BIGINT) AS VARCHAR)|| IF(r.sender IS NOT NULL, '(反向也跨)', '')ORDER BY s.n DESC, s.usd DESC), 1, 6), ';') AS top_sendersFROM per_sender sLEFT JOIN rev r ON r.symbol = s.symbol AND r.route = s.route AND r.sender = s.senderGROUP BY 1, 2)SELECTCASEWHEN UPPER(r.symbol) IN ('USDT', 'USDT0', 'USD₮0', 'USDC', 'USDC.E', 'USDE', 'SUSDE', 'USDG', 'PYUSD', 'USD0', 'USDAI', 'SUSDAI', 'FRXUSD', 'DAI', 'USDS', 'USD1', 'RLUSD', 'AUSD', 'USDTB', 'CUSD', 'EURC') THEN '3 稳定币'WHEN UPPER(r.symbol) IN ('ETH', 'WETH', 'WEETH', 'WSTETH', 'RSETH', 'EZETH', 'STETH', 'BTC', 'WBTC', 'CBBTC', 'BTC.B', 'SOLVBTC', 'LBTC', 'TBTC', 'UBTC', 'UETH', 'SOL', 'WSOL') THEN '2 主流币'ELSE '1 其他币'END AS "类别",r.symbol AS "币", r.project AS "项目", r.route AS "线路",r.n AS "次数", ROUND(r.usd, 0) AS "金额$", r.senders AS "地址数",ROUND(r.med_usd, 0) AS "单笔中位$", ROUND(r.med_sec, 0) AS "到账秒数中位",s.rep_senders AS "重复地址数_5次以上", ROUND(100e0 * s.rep_usd / NULLIF(r.usd, 0e0), 0) AS "重复地址金额占比%",s.rev_senders AS "反向也跨的地址数",s.top_senders AS "跨得最多的地址(完整地址)",r.not_ok AS "没成功次数", r.type_transfer AS "类型",r.src_contract AS "源链合约", r.dst_contract AS "目标链合约"FROM routes rJOIN senders s ON s.symbol = r.symbol AND s.route = r.route