Query editor
WITH discovered_variants AS (-- First discover the top 5 BTC variants by volumeSELECT DISTINCTCASEWHEN token_bought_symbol LIKE '%BTC%' AND token_bought_symbol != 'BTC' THEN token_bought_symbolWHEN token_sold_symbol LIKE '%BTC%' AND token_sold_symbol != 'BTC' THEN token_sold_symbolEND as symbolFROM dex.tradesWHERE(token_bought_symbol LIKE '%BTC%' OR token_sold_symbol LIKE '%BTC%')AND token_bought_symbol != 'BTC' AND token_sold_symbol != 'BTC'AND block_time >= NOW() - INTERVAL '30' day),top_variants AS (SELECTCASEWHEN token_bought_symbol IN (SELECT symbol FROM discovered_variants) THEN token_bought_symbolWHEN token_sold_symbol IN (SELECT symbol FROM discovered_variants) THEN token_sold_symbolEND as variant_name,SUM(amount_usd) as total_volumeFROM dex.tradesWHERE(token_bought_symbol IN (SELECT symbol FROM discovered_variants)OR token_sold_symbol IN (SELECT symbol FROM discovered_variants))AND block_time >= NOW() - INTERVAL '30' dayGROUP BY 1ORDER BY total_volume DESCLIMIT 5),btc_variants_daily_raw AS (SELECTdate_trunc('day', block_time) as day,CASEWHEN token_bought_symbol IN (SELECT variant_name FROM top_variants) THEN token_bought_symbolWHEN token_sold_symbol IN (SELECT variant_name FROM top_variants) THEN token_sold_symbolEND as variant_name,SUM(amount_usd) as daily_volume_usd,COUNT(*) as daily_tradesFROM dex.tradesWHERE(token_bought_symbol IN (SELECT variant_name FROM top_variants)OR token_sold_symbol IN (SELECT variant_name FROM top_variants))AND block_time >= NOW() - INTERVAL '90' dayGROUP BY 1, 2),-- Calculate statistics for anomaly detectionvolume_stats AS (SELECTvariant_name,AVG(daily_volume_usd) as avg_volume,STDDEV(daily_volume_usd) as stddev_volume,APPROX_PERCENTILE(daily_volume_usd, 0.95) as p95_volume,APPROX_PERCENTILE(daily_volume_usd, 0.99) as p99_volume,APPROX_PERCENTILE(daily_volume_usd, 0.90) as p90_volume,MAX(daily_volume_usd) as max_volumeFROM btc_variants_daily_rawWHERE variant_name IS NOT NULL