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,SUM(amount_usd) as total_volume_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 1ORDER BY total_volume_30d DESCLIMIT 10),volume_24h 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_24h_usd,COUNT(*) as trades_24h,COUNT(DISTINCT taker) as unique_traders_24hFROM dex.tradesWHERE(token_bought_symbol IN ('BTC', 'WBTC') OR token_sold_symbol IN ('BTC', 'WBTC'))AND block_time >= NOW() - INTERVAL '24' hourAND token_bought_symbol != token_sold_symbolAND token_bought_symbol IS NOT NULL AND token_sold_symbol IS NOT NULLGROUP BY 1),volume_7d 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_7d_usd,COUNT(*) as trades_7d,COUNT(DISTINCT taker) as unique_traders_7d,AVG(amount_usd) as avg_trade_size_7d,MAX(amount_usd) as max_trade_size_7dFROM dex.tradesWHERE(token_bought_symbol IN ('BTC', 'WBTC') OR token_sold_symbol IN ('BTC', 'WBTC'))AND block_time >= NOW() - INTERVAL '7' dayAND token_bought_symbol != token_sold_symbolAND token_bought_symbol IS NOT NULL AND token_sold_symbol IS NOT NULLGROUP BY 1