Query editor
WITH daily_variant_activity AS (SELECTdate_trunc('day', block_time) as day,CASEWHEN token_bought_symbol = 'WBTC' OR token_sold_symbol = 'WBTC' THEN 'WBTC'WHEN token_bought_symbol = 'cbBTC' OR token_sold_symbol = 'cbBTC' THEN 'cbBTC'WHEN token_bought_symbol = 'tBTC' OR token_sold_symbol = 'tBTC' THEN 'tBTC'WHEN token_bought_symbol = 'sBTC' OR token_sold_symbol = 'sBTC' THEN 'sBTC'WHEN token_bought_symbol = 'renBTC' OR token_sold_symbol = 'renBTC' THEN 'renBTC'END as variant_name,SUM(amount_usd) as daily_volume_usd,COUNT(*) as daily_tradesFROM dex.tradesWHERE(token_bought_symbol IN ('WBTC', 'cbBTC', 'tBTC', 'sBTC', 'renBTC')OR token_sold_symbol IN ('WBTC', 'cbBTC', 'tBTC', 'sBTC', 'renBTC'))AND block_time >= NOW() - INTERVAL '60' dayGROUP BY 1, 2),-- Estimate TVL based on trading volume and variant characteristicsestimated_tvl AS (SELECTday,variant_name,daily_volume_usd,daily_trades,-- TVL estimation methodology:-- WBTC: 25x daily volume (most established)-- cbBTC: 20x daily volume (newer but backed by Coinbase)-- tBTC: 15x daily volume (smaller but growing)-- Others: 10x daily volume (smaller variants)CASEWHEN variant_name = 'WBTC' THEN daily_volume_usd * 25WHEN variant_name = 'cbBTC' THEN daily_volume_usd * 20WHEN variant_name = 'tBTC' THEN daily_volume_usd * 15ELSE daily_volume_usd * 10END as estimated_tvl_usd,-- Calculate 7-day moving average for smoother TVL trendsAVG(CASEWHEN variant_name = 'WBTC' THEN daily_volume_usd * 25WHEN variant_name = 'cbBTC' THEN daily_volume_usd * 20WHEN variant_name = 'tBTC' THEN daily_volume_usd * 15ELSE daily_volume_usd * 10END) OVER (PARTITION BY variant_nameORDER BY dayROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as tvl_7d_ma_usdFROM daily_variant_activityWHERE variant_name IS NOT NULL)SELECTday,variant_name,ROUND(daily_volume_usd / 1e6, 2) as daily_volume_millions_usd,ROUND(estimated_tvl_usd / 1e6, 2) as estimated_tvl_millions_usd,