Query editor
-- Event Bot - Whale Registry v3-- Hedef:-- 1) Her sembol icin en iyi wallet'lari secmek-- 2) Tek bir chain/symbol yigilmasini azaltmak-- 3) Skoru daha dengeli bir registry'ye cevirmekwith target_symbols as (select *from (values('ethereum', 'LINK', 'LINK_USDT'),('arbitrum', 'ARB', 'ARB_USDT'),('optimism', 'OP', 'OP_USDT')) as t(blockchain, token_symbol, required_for_symbols)),relevant_trades as (selectlower(to_hex(t.tx_from)) as wallet,lower(t.blockchain) as blockchain,date_trunc('day', t.block_time) as block_day,t.block_time,ts.required_for_symbols,casewhen upper(t.token_bought_symbol) = ts.token_symbol then 'buy'when upper(t.token_sold_symbol) = ts.token_symbol then 'sell'end as side,cast(t.amount_usd as double) as amount_usdfrom dex.trades tjoin target_symbols tson lower(t.blockchain) = ts.blockchainand (upper(t.token_bought_symbol) = ts.token_symbolor upper(t.token_sold_symbol) = ts.token_symbol)where t.block_time >= now() - interval '30' dayand t.amount_usd is not nulland cast(t.amount_usd as double) >= 150and t.tx_from is not null),wallet_symbol_stats as (selectwallet,blockchain,required_for_symbols,count(*) as trades_30d,count_if(side = 'buy') as buy_trades_30d,count_if(side = 'sell') as sell_trades_30d,count(distinct block_day) as active_days_30d,count(distinct case when side = 'buy' then block_day end) as buy_days_30d,count(distinct case when side = 'sell' then block_day end) as sell_days_30d,sum(case when side = 'buy' then amount_usd else 0 end) as buy_usd_30d,sum(case when side = 'sell' then amount_usd else 0 end) as sell_usd_30d,avg(amount_usd) as avg_ticket_usd_30d,approx_percentile(amount_usd, 0.5) as median_ticket_usd_30d,max(amount_usd) as max_ticket_usd_30dfrom relevant_tradesgroup by 1, 2, 3),