Query editor
WITH s AS (SELECTtx_id,from_asset,to_asset,from_amount_usd,COALESCE(liq_fee_rune_usd, 0) + COALESCE(liq_fee_asset_usd, 0) AS fee_usdFROM thorchain.defi_swapsWHERE block_timestamp >= CURRENT_DATE - INTERVAL '30' dayAND from_amount_usd > 0),tx AS (SELECTtx_id,COUNT(*) AS legs,SUM(fee_usd) AS fee_usd,-- lado de entrada: los legs cuyo from_asset NO es RUNE (suma cubre streaming)SUM(CASE WHEN from_asset NOT LIKE 'THOR.RUNE%' THEN from_amount_usd ELSE 0 END) AS in_usd,MAX(CASE WHEN from_asset NOT LIKE 'THOR.RUNE%'THEN split_part(from_asset, '.', 1) END) AS src,MAX(CASE WHEN to_asset NOT LIKE 'THOR.RUNE%'THEN split_part(to_asset, '.', 1) END) AS dstFROM sGROUP BY tx_id),labelled AS (SELECTCOALESCE(src, 'RUNE') || ' -> ' || COALESCE(dst, 'RUNE') AS route,legs,in_usd,fee_usd,CASEWHEN in_usd < 1000 THEN '1 <$1K'WHEN in_usd < 10000 THEN '2 $1K-10K'WHEN in_usd < 50000 THEN '3 $10K-50K'ELSE '4 >$50K'END AS bucketFROM txWHERE in_usd > 0AND COALESCE(src, 'RUNE') IN ('BTC', 'ETH', 'TRON', 'XRP')AND COALESCE(dst, 'RUNE') IN ('BTC', 'ETH', 'TRON', 'XRP')AND src IS NOT NULL AND dst IS NOT NULL)SELECTroute,bucket,COUNT(*) AS swaps,SUM(in_usd) AS volume_usd,approx_percentile(CAST(legs AS double), 0.5) AS legs_p50,10000 * SUM(fee_usd) / NULLIF(SUM(in_usd), 0) AS cost_bp_endtoendFROM labelledGROUP BY 1, 2HAVING COUNT(*) >= 20ORDER BY 1, 2