Query editor
WITH top_10_variants AS (-- Get the same top 10 variantsSELECTCASEWHEN 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 '% %'AND block_time >= NOW() - INTERVAL '30' dayGROUP BY 1ORDER BY total_volume_30d DESCLIMIT 10),-- Identify potential LP providers based on trading patterns-- LPs typically have: frequent trades, both buy/sell activity, consistent presencepotential_lp_analysis 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,taker as wallet_address,-- Trading pattern analysisCOUNT(*) as total_trades,SUM(amount_usd) as total_volume_usd,COUNT(DISTINCT DATE_TRUNC('day', block_time)) as active_days,-- Buy vs sell balance (LPs should have both)COUNT(CASE WHEN token_bought_symbol IN (SELECT variant_name FROM top_10_variants) THEN 1 END) as buy_trades,COUNT(CASE WHEN token_sold_symbol IN (SELECT variant_name FROM top_10_variants) THEN 1 END) as sell_trades,-- Frequency indicatorsCOUNT(*) / NULLIF(COUNT(DISTINCT DATE_TRUNC('day', block_time)), 0) as trades_per_day,STDDEV(amount_usd) as trade_size_stddev,AVG(amount_usd) as avg_trade_size,-- Time consistencyMAX(block_time) as last_trade,MIN(block_time) as first_tradeFROM 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 '30' dayGROUP BY 1, 2),-- Score and categorize potential LPslp_scoring AS (SELECT*,