Query editor
WITH base_tokens AS (SELECTtoken_address,factoryFROM dune.sealaunch_team.result_base_tokens_factories),token_trades AS (-- Pull trades on tokens created by factoriesSELECTDATE_TRUNC('{{Timeframe}}', block_time) AS week,CASEWHEN token_bought_address IN (SELECT token_address FROM base_tokens)THEN token_bought_addressELSE token_sold_addressEND AS token_address,amount_usd,tx_fromFROM dex.tradesWHERE blockchain = 'base'AND (token_bought_address IN (SELECT token_address FROM base_tokens)OR token_sold_address IN (SELECT token_address FROM base_tokens))),-- First trade week for each trader per factoryfirst_trades AS (SELECTb.factory,t.tx_from,MIN(t.week) AS first_weekFROM token_trades tJOIN base_tokens b ON t.token_address = b.token_addressGROUP BY b.factory, t.tx_from),-- Count how many tokens each trader traded (per week, per factory)trader_token_counts AS (SELECTt.week,b.factory,t.tx_from,COUNT(DISTINCT t.token_address) AS tokens_tradedFROM token_trades tJOIN base_tokens b ON t.token_address = b.token_addressGROUP BY t.week, b.factory, t.tx_from),-- Average number of tokens traded per trader per week per factoryavg_tokens_per_trader AS (SELECTweek,factory,AVG(tokens_traded) AS avg_tokens_per_traderFROM trader_token_countsGROUP BY week, factory),-- Sum volume and count traders per token per week