Query editor
WITH recent AS (SELECT *FROM thorchain.defi_daily_pool_statsWHERE day >= CURRENT_DATE - INTERVAL '30' DAY),ranked AS (SELECTpool_name,day,asset_liquidity * asset_price_usd + rune_liquidity * rune_price_usd AS depth_usd,ROW_NUMBER() OVER (PARTITION BY pool_name ORDER BY day DESC) AS rnFROM recent),latest_depth AS (SELECT pool_name, day AS depth_as_of, depth_usdFROM rankedWHERE rn = 1),agg AS (SELECTpool_name,SUM(swap_volume_rune_usd) AS vol_30d_usd,SUM(total_swap_fees_usd) AS fees_30d_usd,SUM(swap_count) AS swaps_30d,AVG(average_slip) AS avg_slip_bpFROM recentGROUP BY 1)SELECTa.pool_name,l.depth_usd,a.vol_30d_usd,a.swaps_30d,a.vol_30d_usd / NULLIF(a.swaps_30d, 0) AS avg_swap_usd,a.fees_30d_usd,a.fees_30d_usd / NULLIF(a.vol_30d_usd, 0) AS implied_take_rate,a.avg_slip_bp,l.depth_as_ofFROM agg aLEFT JOIN latest_depth l ON a.pool_name = l.pool_nameWHERE a.vol_30d_usd > 0ORDER BY a.vol_30d_usd DESC