Query editor
-- LayerZero 全链"谁在搬"(2026-10-14):源码 oft-xchain-arb/docs/dune/lz-senders.sql,说明见文件头WITH params AS (SELECT CAST('{{start_time}}' AS TIMESTAMP) AS t0, CAST('{{end_time}}' AS TIMESTAMP) AS t1),base AS (SELECT m.addr_from AS sender, m.source_chain AS src, m.destination_chain AS dst, m.source_chain || '→' || m.destination_chain AS route,t.symbol, COALESCE(t.usd_value, m.usd_value) AS usd, m.ts_sourceFROM 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 NULLAND UPPER(t.symbol) NOT IN ('USDT', 'USDT0', 'USD₮0', 'USDC', 'USDC.E', 'USDE', 'SUSDE', 'USDG', 'PYUSD', 'USD0', 'USDAI', 'SUSDAI', 'FRXUSD', 'DAI', 'USDS', 'USD1', 'RLUSD', 'AUSD', 'USDTB', 'CUSD', 'EURC','ETH', 'WETH', 'WEETH', 'WSTETH', 'RSETH', 'EZETH', 'STETH', 'BTC', 'WBTC', 'CBBTC', 'BTC.B', 'SOLVBTC', 'LBTC', 'TBTC', 'UBTC', 'UETH', 'SOL', 'WSOL')),per AS (SELECT b.sender, b.symbol, b.route, COUNT(*) AS n, SUM(b.usd) AS usd FROM base b GROUP BY 1, 2, 3),both_dir AS (SELECT x.sender, COUNT(DISTINCT x.symbol) AS n_bothFROM (SELECT DISTINCT sender, symbol, src, dst FROM base) xJOIN (SELECT DISTINCT sender, symbol, src, dst FROM base) y ON y.sender = x.sender AND y.symbol = x.symbol AND y.src = x.dst AND y.dst = x.srcGROUP BY 1),agg AS (SELECT p.sender, SUM(p.n) AS n, SUM(p.usd) AS usd, COUNT(DISTINCT p.symbol) AS n_tokens, COUNT(*) AS n_routes,ARRAY_JOIN(SLICE(ARRAY_AGG(p.symbol || ' ' || p.route || ' ' || CAST(p.n AS VARCHAR) || '次 $' || CAST(CAST(ROUND(COALESCE(p.usd, 0e0), 0) AS BIGINT) AS VARCHAR)ORDER BY p.n DESC, p.usd DESC), 1, 10), ';') AS topFROM per p GROUP BY 1),span AS (SELECT b.sender, MIN(b.ts_source) AS first_t, MAX(b.ts_source) AS last_t,APPROX_PERCENTILE(b.usd, 0.5) AS med_usd,ARRAY_JOIN(ARRAY_SORT(ARRAY_DISTINCT(ARRAY_AGG(b.src) || ARRAY_AGG(b.dst))), '、') AS chainsFROM base b GROUP BY 1)SELECTSUBSTR(a.sender, LENGTH(a.sender) - 3, 4) AS "尾号", a.sender AS "地址",a.n AS "跨链次数", ROUND(a.usd, 0) AS "金额$", ROUND(s.med_usd, 0) AS "单笔中位$",a.n_tokens AS "币数", a.n_routes AS "币×线路数", COALESCE(d.n_both, 0) AS "两个方向都跨的币数",s.chains AS "用到的链",a.top AS "币和线路(次数最多的 10 个)",s.first_t AS "第一次", s.last_t AS "最后一次"FROM agg aJOIN span s ON s.sender = a.senderLEFT JOIN both_dir d ON d.sender = a.senderWHERE a.n >= {{min_trips}}ORDER BY a.n DESC