Query editor
WITH s AS (SELECTsplit_part(pool_name, '.', 1) AS corridor,from_amount_usd,swap_slip_bp,COALESCE(liq_fee_rune_usd, 0) + COALESCE(liq_fee_asset_usd, 0) AS liq_fee_usd,affiliate_fee_basis_points,COALESCE(streaming_count, 0) AS streaming_count,CASEWHEN from_amount_usd < 100 THEN '1 <$100'WHEN from_amount_usd < 1000 THEN '2 $100-1K'WHEN from_amount_usd < 10000 THEN '3 $1K-10K'WHEN from_amount_usd < 50000 THEN '4 $10K-50K'ELSE '5 >$50K'END AS bucketFROM thorchain.defi_swapsWHERE block_timestamp >= CURRENT_DATE - INTERVAL '30' dayAND from_amount_usd > 0AND split_part(pool_name, '.', 1) IN ('BTC', 'ETH', 'TRON', 'XRP'))SELECTcorridor,bucket,COUNT(*) AS swaps,SUM(from_amount_usd) AS volume_usd,approx_percentile(swap_slip_bp, 0.5) AS slip_bp_p50,approx_percentile(swap_slip_bp, 0.9) AS slip_bp_p90,10000 * SUM(liq_fee_usd) / NULLIF(SUM(from_amount_usd), 0) AS liq_fee_bp,100.0 * SUM(CASE WHEN streaming_count > 1 THEN 1 ELSE 0 END) / COUNT(*) AS pct_streaming,approx_percentile(CAST(affiliate_fee_basis_points AS double), 0.5) AS affiliate_bp_p50,100.0 * SUM(CASE WHEN affiliate_fee_basis_points > 0 THEN 1 ELSE 0 END) / COUNT(*) AS pct_with_affiliateFROM sGROUP BY 1, 2ORDER BY 1, 2