Query editor
WITH top_10_pairs 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,COUNT(*) as total_tradesFROM 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 1ORDER BY total_trades DESCLIMIT 10),daily_pair_trades AS (SELECTdate_trunc('day', block_time) as day,CASEWHEN 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,COUNT(*) as daily_trades,COUNT(DISTINCT taker) as daily_unique_traders,AVG(amount_usd) as avg_trade_size_usdFROM 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, 2)SELECTday,trading_pair,daily_trades,daily_unique_traders,ROUND(avg_trade_size_usd, 2) as avg_trade_size_usd,-- 7-day moving average for tradesROUND(AVG(daily_trades) OVER (PARTITION BY trading_pairORDER BY dayROWS BETWEEN 6 PRECEDING AND CURRENT ROW), 1) as trades_7d_maFROM daily_pair_tradesWHERE trading_pair IN (SELECT trading_pair FROM top_10_pairs)ORDER BY day ASC, daily_trades DESC;