Query editor
with graduated as (select token, name, symbol, min(block_time) as graduated_date from(selectblock_time, account_arguments[3] AS tokenfrom solana.instruction_callswhereexecuting_account = '6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P'and BYTEARRAY_SUBSTRING(data, 1, 8) = 0x9beae792ec9ea21eand block_slot >= 328034480and tx_successunionselectblock_time, account_arguments[2] AS tokenfrom solana.instruction_callswhereexecuting_account = '6EF8rrecthR5Dkzon8Nwu78hRvfCKubJ14M5uBEwF6P'and BYTEARRAY_SUBSTRING(data, 1, 8) = 0x9beae792ec9ea21eand block_slot >= 328034480and tx_success) g left join tokens_solana.fungible f on g.token = f.token_mint_addressgroup by 1,2,3),prices_raw as (select block_time, token,cast(amount_usd as double)/cast(token_amount as double) as token_price,cast(amount_usd as double)/cast(token_amount as double) * 1000000000 as mcap,token_amount as token_amount,tx_idfrom(select block_time, tx_id, token_bought_mint_address as token,amount_usd, token_bought_amount as token_amountfrom dex_solana.trades where token_bought_mint_address in (select distinct token from graduated) and amount_usd is not null and amount_usd >= 1 and token_bought_amount is not null and block_slot >= 328034480union allselect block_time, tx_id, token_sold_mint_address as token,amount_usd, token_sold_amount as token_amountfrom dex_solana.trades where token_sold_mint_address in (select distinct token from graduated) and amount_usd is not null and amount_usd >= 1 and token_sold_amount is not null and block_slot >= 328034480)),hourly_weighted_prices AS (SELECTDATE_TRUNC('hour', block_time) AS hour,token,sum(token_price*token_amount)/sum(token_amount) as weighted_price,sum(token_price*token_amount)/sum(token_amount) * 1000000000 as weighted_mcap,count(distinct tx_id) as tradesFROM prices_rawGROUP BY 1, 2),prices as (select block_time, r.token, token_price, weighted_price, mcap, weighted_mcap, tradesfrom prices_raw r left join hourly_weighted_prices w on DATE_TRUNC('hour', block_time) = hour and r.token = w.tokenwhere (trades > 100 or (trades > 2 and mcap < 50000)) and token_price < 1 and mcap/weighted_mcap < 10),mcap as (select g.token, g.symbol, g.graduated_date, max(mcap) as ath,max(case when block_time >= current_timestamp - interval '24' hour then mcap end) as max_mcap_24hsfrom graduated g left join prices p on g.token = p.token