Query editor
--RESOURCES:--1) https://pump.fun/board--2) Pump.fun program: https://solscan.io/account/6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P--GOAL: Find avg FDV for each pump.fun creator--Parameters:-- Token Address-- Token Supply-- Token Price-- Creator AddressWITH pump_tokens as (SELECTtr.token_mint_address, amount/POWER(10,6) as total_supply, tx_signer as token_creatorFROM tokens_solana.transfers tr -- Curated Table of all token transfers--LEFT JOIN prices.usd_latest pr on tr.token_mint_address = toBase58(pr.contract_address)WHERE action = 'mint' -- Filter for token mintsAND outer_executing_account = '6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P' --pump.fun programAND tr.token_mint_address is NOT NULL -- clean up dataAND block_time >= timestamp '2024-01-01'),latest_usd_dex_prices as (SELECT * FROM (SELECTROW_NUMBER() OVER(PARTITION BY token_bought_mint_address ORDER BY block_time DESC) as swap_rank, token_bought_mint_address, token_bought_symbol as symbol, amount_usd/token_bought_amount as price_usd, projectFROM dex_solana.trades aINNER JOIN pump_tokens b on a.token_bought_mint_address = b.token_mint_addressWHERE amount_usd is NOT NULLAND token_bought_symbol IS NOT NULLAND token_bought_symbol != 'COOL💹'--AND project = 'raydium'AND block_time >= timestamp '2024-01-01') WHERE swap_rank = 1)SELECTROW_NUMBER() OVER(ORDER BY AVG(total_supply * price_usd) DESC) as creator_rank, a.token_creator, AVG(total_supply * price_usd) as avg_fdv, approx_percentile(total_supply * price_usd, 0.5) as median_fdv, COUNT(DISTINCT token_mint_address) as tokens_created, ARRAY_AGG(pr.symbol) as tokensFROM pump_tokens aINNER JOIN latest_usd_dex_prices pr ON a.token_mint_address = pr.token_bought_mint_addressWHERE price_usd < 0.5 --filter out outlier swapsAND project != 'pumpdotfun' --remove any tokens where the last swap was on pumpdotfun. Have not traded on DEX yet.AND token_mint_address != '5rTp9dW8ZQL4AKRmGLHhPu7vb14ZXXX5Gb2qF1Vnpump'GROUP BY 2ORDER BY 1limit 1000