Query editor
WITH time_range AS (SELECT current_timestamp - interval '{{hour/days_ago}}' {{day/hour}} AS start_time,current_timestamp AS end_time),pump_tokens AS (SELECTtx_signer AS dev_address,token_mint_address,block_time AS creation_dateFROM tokens_solana.transfers, time_rangeWHERE action = 'mint'AND outer_executing_account = '6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P' -- pump.fun programAND token_mint_address IS NOT NULLAND block_time >= start_timeAND block_time < end_time),token_prices AS (SELECTa.token_bought_mint_address,a.token_bought_symbol,a.amount_usd / NULLIF(a.token_bought_amount, 0) AS price,a.block_timeFROM dex_solana.trades aJOIN pump_tokens b ON a.token_bought_mint_address = b.token_mint_addressJOIN time_range t ON a.block_time >= t.start_time AND a.block_time < t.end_timeWHERE a.amount_usd IS NOT NULLAND a.token_bought_amount IS NOT NULLAND a.token_bought_symbol IS NOT NULL),token_metrics AS (SELECTp.dev_address,p.token_mint_address,p.creation_date,COALESCE(MAX(CASE WHEN is_finite(t.price * 1e9) THEN t.price * 1e9 ELSE 0 END), 0) AS ath_market_cap,COALESCE(AVG(CASE WHEN is_finite(t.price * 1e9) THEN t.price * 1e9 ELSE 0 END), 0) AS avg_market_cap,CASE WHEN COALESCE(MAX(CASE WHEN is_finite(t.price * 1e9) THEN t.price * 1e9 ELSE 0 END), 0) >= 69000 THEN 1 ELSE 0 END AS is_successfulFROM pump_tokens pLEFT JOIN token_prices t ON p.token_mint_address = t.token_bought_mint_addressGROUP BY p.dev_address, p.token_mint_address, p.creation_date),dev_metrics AS (SELECTdev_address,COUNT(token_mint_address) AS tokens_deployed,MAX(ath_market_cap) AS ath_market_cap,AVG(avg_market_cap) AS avg_market_cap,SUM(is_successful) * 100.0 / NULLIF(COUNT(token_mint_address), 0) AS success_rate,MAX(creation_date) AS last_token_dateFROM token_metricsGROUP BY dev_address),wallet_balance AS (SELECTaddress AS dev_address,