Query editor
-- Robinhood 链 DEX 赚钱排行榜(v2,源码见仓库 config/dune/dex-profit-leaderboard.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('{{min_trades}}' AS INTEGER), 5) AS min_trades,COALESCE(TRY_CAST('{{max_senders}}' AS INTEGER), 30) AS max_senders),tx_scan AS (SELECT t."from" AS sender, t."to" AS callee, t.success,CAST(t.gas_used AS DOUBLE) * CAST(COALESCE(t.effective_gas_price, t.gas_price) AS DOUBLE) / 1e18 AS fee_ethFROM robinhood.transactions tCROSS JOIN params pWHERE t.block_date >= CAST(p.start_ts AS DATE) AND t.block_date < CAST(p.end_ts AS DATE)AND t.block_time >= p.start_ts AND t.block_time < p.end_ts),private_callee AS (SELECT s.calleeFROM tx_scan sWHERE s.success AND s.callee IS NOT NULLGROUP BY 1HAVING COUNT(DISTINCT s.sender) <= (SELECT max_senders FROM params)),ethpx AS (SELECT COALESCE((SELECT AVG(pr.price) FROM prices.minute pr CROSS JOIN params pWHERE pr.blockchain = 'robinhood' AND pr.contract_address = 0x0bd7d308f8e1639fab988df18a8011f41eacad73AND pr."timestamp" >= p.start_ts AND pr."timestamp" < p.end_ts),(SELECT AVG(pr.price) FROM prices.minute pr CROSS JOIN params pWHERE pr.blockchain = 'ethereum' AND pr.contract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2AND pr."timestamp" >= p.start_ts AND pr."timestamp" < p.end_ts)) AS px),f0 AS (SELECTtr.tx_hash,tr.tx_from AS ent,IF(tr.token_standard = 'native', 0x0bd7d308f8e1639fab988df18a8011f41eacad73, tr.contract_address) AS asset,MIN(tr.block_number) AS bn,MIN(tr.block_time) AS bt,MIN(tr.tx_to) AS callee,SUM(a.sgn * COALESCE(tr.amount, CAST(tr.amount_raw AS DOUBLE) / 1e18)) AS net,SUM(ABS(COALESCE(tr.amount, CAST(tr.amount_raw AS DOUBLE) / 1e18))) AS gross,SUM(a.sgn * tr.amount_usd) AS net_usd,COUNT_IF(tr.amount_usd IS NULL) AS unpricedFROM tokens.transfers trCROSS JOIN params pCROSS JOIN UNNEST(ARRAY[tr."to", tr."from"], ARRAY[1e0, -1e0]) AS a(addr, sgn)LEFT JOIN private_callee pc ON pc.callee = tr.tx_toWHERE tr.blockchain = 'robinhood'AND tr.block_month >= CAST(DATE_TRUNC('month', p.start_ts) AS DATE)AND tr.block_time >= p.start_ts AND tr.block_time < p.end_tsAND (a.addr = tr.tx_from OR (a.addr = tr.tx_to AND pc.callee IS NOT NULL))AND NOT ((tr.token_standard = 'native' OR tr.contract_address = 0x0bd7d308f8e1639fab988df18a8011f41eacad73)AND (tr."from" IN (0x0bd7d308f8e1639fab988df18a8011f41eacad73, 0x0000000000000000000000000000000000000000)OR tr."to" IN (0x0bd7d308f8e1639fab988df18a8011f41eacad73, 0x0000000000000000000000000000000000000000)))GROUP BY 1, 2, 3),f1 AS (SELECT