Query editor
WITH top_10_variants AS (-- Get the same top 10 variants with data quality filtersSELECTCASEWHEN 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 variant_name,SUM(amount_usd) as total_volume_30dFROM dex.tradesWHERE(token_bought_symbol LIKE '%BTC%' OR token_sold_symbol LIKE '%BTC%')AND token_bought_symbol != 'BTC' AND token_sold_symbol != 'BTC'AND LENGTH(COALESCE(token_bought_symbol, token_sold_symbol)) <= 10AND COALESCE(token_bought_symbol, token_sold_symbol) NOT LIKE '%/%'AND COALESCE(token_bought_symbol, token_sold_symbol) NOT LIKE '% %'-- Exclude known problematic variantsAND COALESCE(token_bought_symbol, token_sold_symbol) NOT IN ('TBTC') -- Corrupted dataAND block_time >= NOW() - INTERVAL '30' day-- Data quality filter: reasonable trade amountsAND amount_usd BETWEEN 1 AND 1000000000 -- $1 to $1B per tradeGROUP BY 1HAVING SUM(amount_usd) > 1000000 -- Minimum $1M total volumeORDER BY total_volume_30d DESCLIMIT 10),-- Calculate daily volume-weighted average prices for each variantdaily_prices AS (SELECTdate_trunc('day', block_time) as day,CASEWHEN 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,-- Calculate volume-weighted average priceSUM(amount_usd) as daily_volume_usd,COUNT(*) as daily_trades,-- For price calculation, we need to estimate the token price-- Use the USD amount divided by estimated token amount-- This is an approximation since we don't have exact token amountsSUM(amount_usd) / COUNT(*) as avg_trade_value_usd,-- Get BTC price for the day to use as referenceAVG(CASEWHEN token_bought_symbol = 'BTC' THEN amount_usd / NULLIF(token_bought_amount, 0)WHEN token_sold_symbol = 'BTC' THEN amount_usd / NULLIF(token_sold_amount, 0)ELSE NULLEND) as btc_reference_priceFROM 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 '60' dayGROUP BY 1, 2),