Query editor
WITH btc_pairs_volume 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 volume_30d_usd,COUNT(*) as trades_30dFROM dex.tradesWHERE(token_bought_symbol IN ('BTC', 'WBTC') OR token_sold_symbol IN ('BTC', 'WBTC'))AND block_time >= NOW() - INTERVAL '30' dayAND token_bought_symbol != token_sold_symbolAND token_bought_symbol IS NOT NULL AND token_sold_symbol IS NOT NULLGROUP BY 1),total_btc_volume AS (SELECT SUM(volume_30d_usd) as total_volume_30dFROM btc_pairs_volumeWHERE trading_pair IS NOT NULL)SELECTtrading_pair,ROUND(volume_30d_usd / 1e6, 2) as volume_30d_millions_usd,trades_30d,ROUND((volume_30d_usd / tv.total_volume_30d) * 100, 2) as market_share_pct,-- Cumulative market shareROUND(SUM(volume_30d_usd) OVER (ORDER BY volume_30d_usd DESC) / tv.total_volume_30d * 100, 2) as cumulative_market_share_pct,ROUND(tv.total_volume_30d / 1e6, 2) as total_btc_pairs_volume_millionsFROM btc_pairs_volume pCROSS JOIN total_btc_volume tvWHERE p.trading_pair IS NOT NULLORDER BY volume_30d_usd DESCLIMIT 15;