Query editor
WITH token_holders AS (-- Step 1: Extract all relevant transactions for POPCAT tokenSELECTDATE(block_time) AS trade_date,token_bought_mint_address AS holder_address,token_bought_symbol,token_sold_symbol,CASEWHEN token_bought_symbol = 'POPCAT' THEN token_bought_amountWHEN token_sold_symbol = 'POPCAT' THEN -token_sold_amountELSE 0END AS balance_changeFROMdex_solana.tradesWHEREtoken_bought_symbol = 'POPCAT' OR token_sold_symbol = 'POPCAT'),daily_balances AS (-- Step 2: Aggregate daily balance changes for each holderSELECTtrade_date,holder_address,SUM(balance_change) OVER (PARTITION BY holder_address ORDER BY trade_date) AS cumulative_balanceFROMtoken_holders),holder_status AS (-- Step 3: Determine whether a holder is active (balance > 0)SELECTtrade_date,holder_address,CASEWHEN cumulative_balance > 0 THEN 1ELSE 0END AS is_activeFROMdaily_balances),daily_holder_count AS (-- Step 4: Count the total number of active holders each daySELECTtrade_date,SUM(is_active) AS active_holdersFROMholder_statusGROUP BYtrade_date)-- Final Output: Token holder evolution over timeSELECTtrade_date AS "Date",active_holders AS "Number of Active Holders"FROMdaily_holder_countORDER BYtrade_date ASC;