Query editor
-- Live foregone fees by protocol on a consistent 26-week-average basis.-- Uni v3 + Pancake v3: avg OOR TVL x blended in-range APR (APR = 30d volume x per-pool fee tier, annualized).-- Aerodrome: avg OOR TVL x 22.5% (midpoint of a 15-30% band; Slipstream fees are dynamic, not a fixed tier).-- foregone_musd_yr holds full dollars (format in the chart).WITHtvl AS (SELECT protocol, AVG(wk_inrange) AS avg_inrange, AVG(wk_oor) AS avg_oorFROM (SELECT protocol, d,SUM(CASE WHEN in_range THEN total_usd ELSE 0 END) AS wk_inrange,SUM(CASE WHEN NOT in_range THEN total_usd ELSE 0 END) AS wk_oorFROM dune.dune_curated_data.result_idle_h1_combinedWHERE protocol IN ('uniswap_v3','pancakeswap_v3','aerodrome_slipstream')GROUP BY 1,2) GROUP BY 1),panel AS (SELECT DISTINCT protocol, chain, pool AS pool_hexFROM dune.dune_curated_data.result_idle_h1_combinedWHERE protocol IN ('uniswap_v3','pancakeswap_v3')),vol AS (SELECT CASE WHEN project='uniswap' THEN 'uniswap_v3' ELSE 'pancakeswap_v3' END AS protocol,blockchain AS chain, '0x'||to_hex(project_contract_address) AS pool_hex, SUM(amount_usd) vol30dFROM dex.tradesWHERE ((project='uniswap' AND version='3' AND blockchain IN ('ethereum','base','arbitrum','unichain','polygon','optimism'))OR (project='pancakeswap' AND version='3' AND blockchain IN ('bnb','base','arbitrum','ethereum')))AND block_date >= DATE '2026-05-31' AND block_time <= TIMESTAMP '2026-06-30 12:00:00' AND amount_usd>0GROUP BY 1,2,3),fee AS (SELECT 'uniswap_v3' AS protocol, chain, '0x'||to_hex(pool) AS pool_hex, fee FROM uniswap_v3_multichain.uniswapv3factory_evt_poolcreated WHERE chain IN ('ethereum','base','arbitrum','unichain','polygon','optimism')UNION ALLSELECT 'pancakeswap_v3', chain, '0x'||to_hex(pool), fee FROM pancakeswap_v3_multichain.pancakev3factory_evt_poolcreated WHERE chain IN ('bnb','base','arbitrum','ethereum')),fees AS (SELECT p.protocol, SUM(COALESCE(v.vol30d,0) * (CAST(f.fee AS DOUBLE)/1e6) * (365.0/30.0)) AS annual_feesFROM panel pJOIN fee f ON f.protocol=p.protocol AND f.chain=p.chain AND f.pool_hex=p.pool_hexLEFT JOIN vol v ON v.protocol=p.protocol AND v.chain=p.chain AND v.pool_hex=p.pool_hexGROUP BY 1),calc AS (SELECT t.protocol, t.avg_oor * fs.annual_fees / NULLIF(t.avg_inrange,0) AS foregoneFROM tvl t JOIN fees fs ON fs.protocol=t.protocolUNION ALLSELECT t.protocol, t.avg_oor * 0.225 AS foregoneFROM tvl t WHERE t.protocol='aerodrome_slipstream')SELECT ord, protocol, ROUND(foregone,0) AS foregone_musd_yrFROM (SELECT 1 AS ord, 'Uniswap v3' AS protocol, foregone FROM calc WHERE protocol='uniswap_v3'UNION ALL SELECT 2, 'PancakeSwap v3', foregone FROM calc WHERE protocol='pancakeswap_v3'UNION ALL SELECT 3, 'Aerodrome (bounded)', foregone FROM calc WHERE protocol='aerodrome_slipstream') ORDER BY ord