Query editor
WITH discovered_btc_variants AS (-- Dynamic discovery of BTC variantsSELECT DISTINCTtoken_bought_symbol as symbolFROM dex.tradesWHERE token_bought_symbol LIKE '%BTC%'AND token_bought_symbol != 'BTC'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 block_time >= NOW() - INTERVAL '30' day),filtered_variants AS (SELECT DISTINCT symbolFROM discovered_btc_variantsWHERE symbol IS NOT NULLAND LENGTH(symbol) <= 10AND symbol NOT LIKE '%/%'AND symbol NOT LIKE '% %'),top_5_variants AS (SELECTCASEWHEN token_bought_symbol IN (SELECT symbol FROM filtered_variants) THEN token_bought_symbolWHEN token_sold_symbol IN (SELECT symbol FROM filtered_variants) THEN token_sold_symbolEND as variant_name,SUM(amount_usd) as total_volumeFROM dex.tradesWHERE(token_bought_symbol IN (SELECT symbol FROM filtered_variants)OR token_sold_symbol IN (SELECT symbol FROM filtered_variants))AND block_time >= NOW() - INTERVAL '30' dayGROUP BY 1ORDER BY total_volume DESCLIMIT 5),daily_variant_volumes_raw AS (SELECTdate_trunc('day', block_time) as day,CASEWHEN token_bought_symbol IN (SELECT variant_name FROM top_5_variants) THEN token_bought_symbolWHEN token_sold_symbol IN (SELECT variant_name FROM top_5_variants) THEN token_sold_symbolEND as variant_name,SUM(amount_usd) as daily_volume_usdFROM dex.tradesWHERE(token_bought_symbol IN (SELECT variant_name FROM top_5_variants)OR token_sold_symbol IN (SELECT variant_name FROM top_5_variants))AND block_time >= NOW() - INTERVAL '60' dayGROUP BY 1, 2),