Query editor
WITH discovered_btc_variants AS (-- Dynamic discovery of all BTC variantsSELECT DISTINCTtoken_bought_symbol as symbolFROM dex.tradesWHERE token_bought_symbol LIKE '%BTC%'AND token_bought_symbol != 'BTC'AND LENGTH(token_bought_symbol) <= 10AND token_bought_symbol NOT LIKE '%/%'AND token_bought_symbol NOT LIKE '% %'AND block_time >= NOW() - INTERVAL '30' dayUNION ALLSELECT DISTINCTtoken_sold_symbol as symbolFROM dex.tradesWHERE token_sold_symbol LIKE '%BTC%'AND token_sold_symbol != 'BTC'AND LENGTH(token_sold_symbol) <= 10AND token_sold_symbol NOT LIKE '%/%'AND token_sold_symbol NOT LIKE '% %'AND block_time >= NOW() - INTERVAL '30' day),-- Get top 10 variants by 30-day volumetop_10_variants AS (SELECTCASEWHEN token_bought_symbol IN (SELECT symbol FROM discovered_btc_variants) THEN token_bought_symbolWHEN token_sold_symbol IN (SELECT symbol FROM discovered_btc_variants) THEN token_sold_symbolEND as variant_name,SUM(amount_usd) as total_volume_30dFROM dex.tradesWHERE(token_bought_symbol IN (SELECT symbol FROM discovered_btc_variants)OR token_sold_symbol IN (SELECT symbol FROM discovered_btc_variants))AND block_time >= NOW() - INTERVAL '30' dayGROUP BY 1ORDER BY total_volume_30d DESCLIMIT 10),-- Calculate 24h volumevolume_24h AS (SELECTCASEWHEN token_bought_symbol IN (SELECT variant_name FROM top_10_variants) THEN token_bought_symbolWHEN token_sold_symbol IN (SELECT variant_name FROM top_10_variants) THEN token_sold_symbolEND as variant_name,SUM(amount_usd) as volume_24h_usd,COUNT(*) as trades_24h,COUNT(DISTINCT taker) as unique_traders_24hFROM dex.tradesWHERE(token_bought_symbol IN (SELECT variant_name FROM top_10_variants)OR token_sold_symbol IN (SELECT variant_name FROM top_10_variants))AND block_time >= NOW() - INTERVAL '24' hourGROUP BY 1),