Query editor
WITH btc_pairs_raw AS (SELECTCASEWHEN token_bought_symbol = 'BTC' THEN CONCAT(token_sold_symbol, '-BTC')WHEN token_sold_symbol = 'BTC' THEN CONCAT(token_bought_symbol, '-BTC')WHEN token_bought_symbol = 'WBTC' THEN CONCAT(token_sold_symbol, '-WBTC')WHEN token_sold_symbol = 'WBTC' THEN CONCAT(token_bought_symbol, '-WBTC')END as trading_pair,SUM(amount_usd) as total_volume_30d,COUNT(*) as total_trades_30d,COUNT(DISTINCT taker) as unique_traders_30d,AVG(amount_usd) as avg_trade_size_30d,MAX(amount_usd) as max_trade_size_30dFROM dex.tradesWHERE(token_bought_symbol IN ('BTC', 'WBTC') OR token_sold_symbol IN ('BTC', 'WBTC'))AND block_time >= NOW() - INTERVAL '30' day-- Filter out self-pairs and null symbolsAND token_bought_symbol != token_sold_symbolAND token_bought_symbol IS NOT NULLAND token_sold_symbol IS NOT NULLAND LENGTH(token_bought_symbol) <= 10AND LENGTH(token_sold_symbol) <= 10GROUP BY 1),top_10_pairs AS (SELECT *FROM btc_pairs_rawWHERE trading_pair IS NOT NULLORDER BY total_volume_30d DESCLIMIT 10)SELECTtrading_pair,ROUND(total_volume_30d / 1e6, 2) as volume_30d_millions_usd,total_trades_30d,unique_traders_30d,ROUND(avg_trade_size_30d, 2) as avg_trade_size_usd,ROUND(max_trade_size_30d / 1e6, 2) as max_trade_size_millions,ROUND((total_volume_30d / SUM(total_volume_30d) OVER ()) * 100, 2) as market_share_pct,ROW_NUMBER() OVER (ORDER BY total_volume_30d DESC) as pair_rankFROM top_10_pairsORDER BY total_volume_30d DESC;