Query editor
-- Robinhood 链 DEX 交易者排行(dex.trades 版 v2,源码见仓库 config/dune/dex-trades-leaderboard.sql)-- 排行 = 现金净流入(ETH/WETH/USDG),只排窗口内买入额 ≥ 卖出额 30% 的往返交易者或原子套利 ≥ 5 笔;持仓单列参考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('{{min_trades}}' AS INTEGER), 10) AS min_trades),t AS (SELECTd.tx_from, d.tx_hash, d.block_time, d.project, d.amount_usd,IF(d.token_bought_address = 0x0000000000000000000000000000000000000000, 0x0bd7d308f8e1639fab988df18a8011f41eacad73, d.token_bought_address) AS tb,IF(d.token_sold_address = 0x0000000000000000000000000000000000000000, 0x0bd7d308f8e1639fab988df18a8011f41eacad73, 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 = 'robinhood'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 IS NOT NULL),legs AS (SELECT t.tx_from, t.tx_hash, t.block_time, t.project, t.amount_usd, x.asset, x.qty,(x.asset IN (0x0bd7d308f8e1639fab988df18a8011f41eacad73, 0x5fc5360d0400a0fd4f2af552add042d716f1d168)) AS is_cash,x.sgnFROM tCROSS JOIN UNNEST(ARRAY[t.tb, t.ts], ARRAY[t.ab, -t.asold], ARRAY[1e0, -1e0]) AS x(asset, qty, sgn)),tok_px AS (SELECT asset, APPROX_PERCENTILE(px, 0.5) AS px_endFROM (SELECT l.asset, l.amount_usd / ABS(l.qty) AS px,ROW_NUMBER() OVER (PARTITION BY l.asset ORDER BY l.block_time DESC) AS rnFROM legs lWHERE NOT l.is_cash AND l.qty <> 0e0 AND l.amount_usd >= 10e0) zWHERE rn <= 20GROUP BY 1),tx_asset AS (SELECT tx_from, tx_hash, asset, BOOL_OR(is_cash) AS is_cash,SUM(qty) AS qty, SUM(IF(is_cash, sgn * amount_usd, 0e0)) AS cash_usd, SUM(ABS(qty)) AS gross,MIN(block_time) AS btFROM legsGROUP BY 1, 2, 3),tx_lvl AS (SELECT tx_from, tx_hash, MIN(bt) AS bt,SUM(cash_usd) AS cash,COUNT_IF(NOT is_cash) AS toks,COUNT_IF(NOT is_cash AND ABS(qty) > 1e-6 * gross) AS toks_changedFROM tx_assetGROUP BY 1, 2),ent_tok AS (SELECT a.tx_from, a.asset, SUM(a.qty) AS inv, COUNT(*) AS txsFROM tx_asset aWHERE NOT a.is_cashGROUP BY 1, 2),