-- forked from holders_main_bot @ https://flipsidecrypto.xyz/studio/queries/6d81e367-c549-4090-b5c7-25c85a2e2d49 -- forked from holders_main @ https://flipsidecrypto.xyz/studio/queries/fcad9996-75f3-4c5e-a92c-29bbbbf6751d -- forked from holders-10sec interval @ https://flipsidecrypto.xyz/studio/queries/2d00764c-2973-4e6e-a77c-27242887a5d9 --SET my_ca='21bZHq4a1sjkwfS15VBot2S5iZRfE51XVTGdCTGKpump'; /*select dateadd(HOUR, -5, c.block_timestamp) AS ts, * FROM solana.core.fact_token_balances c where c.mint = 'BmXfbamFqrBzrqihr9hbSmEsfQUXMVaqshAjgvZupump' AND dateadd(HOUR, -5, c.block_timestamp) > '2025-01-30 00:00' AND dateadd(HOUR, -5, c.block_timestamp) < '2025-02-02 23:59' order by c.block_timestamp asc limit 100*/ WITH cte_ca as ( select 'H87h6L5e7DTux8QyaEFaHivY6gSpSxfJd6hyhQpvpump' as ca ), cte_stg as ( select dateadd(HOUR, -5, c.block_timestamp) AS ts, round(pre_balance,0) as pre_balance, round(balance,0) as balance, owner, c.tx_id, mint, dex.swapper, dex.swap_from_mint, dex.swap_from_symbol, dex.swap_from_amount_usd, dex.swap_to_mint, dex.swap_to_symbol, dex.swap_to_amount_usd, case when dex.swap_from_amount > 100000000 then 1 else dex.swap_from_amount end as swap_from_amount , case when dex.swap_to_amount > 100000000 then 1 else dex.swap_to_amount end as swap_to_amount, FROM solana.core.fact_token_balances c left join solana.defi.ez_dex_swaps dex on c.tx_id = dex.tx_id --and dateadd(HOUR, -5, dex.block_timestamp) > '2025-02-06 00:00' and dateadd(HOUR, -5, dex.block_timestamp) < '2025-02-08 23:59' and c.block_timestamp = dex.block_timestamp WHERE mint = (select ca from cte_ca) --AND BLOCK_TIMESTAMP > '2025-01-23 00:00' --AND BLOCK_TIMESTAMP < '2025-01-25 03:59' --AND dateadd(HOUR, -5, c.block_timestamp) > '2025-01-27 00:00' AND dateadd(HOUR, -5, c.block_timestamp) > '2025-02-06 00:00' AND dateadd(HOUR, -5, c.block_timestamp) < '2025-02-08 23:59' --AND dateadd(HOUR, -5, c.block_timestamp) > '2024-12-17 00:00' --AND dateadd(HOUR, -5, c.block_timestamp) < '2024-12-19 08:52' ) --select * from cte_stg order by ts ASC --limit 100 , cte AS ( SELECT ts, row_number() over(partition by tx_id order by ts asc) as rn_tx, CASE WHEN pre_balance = 0 AND balance > 0 THEN 1 WHEN pre_balance > 0 THEN CASE WHEN balance / pre_balance - 1 < -0.97 THEN -1 ELSE 0 END END AS holder, CASE WHEN pre_balance = 0 AND balance > 0 THEN 1 WHEN pre_balance > 0 AND balance = 0 THEN -1 ELSE 0 END AS holder_orig, CASE WHEN balance > pre_balance THEN 1 ELSE 0 END AS buys, CASE WHEN balance < pre_balance THEN 1 ELSE 0 END AS sells, case when pre_balance >= 1000 AND balance < 1000 then 1 else 0 end as sold_out, case when pre_balance < 1000 AND balance >= 1000 then 1 else 0 end as new_buy, pre_balance, balance, balance - pre_balance AS net, owner, tx_id, mint, swapper, swap_from_mint, swap_to_mint, swap_from_amount_usd, swap_to_amount_usd, swap_from_amount, swap_to_amount, --max(ifnull(swap_from_amount_usd,0),ifnull(swap_to_amount_usd,0)) as usd_val, case when ifnull(swap_from_amount_usd,0) > ifnull(swap_to_amount_usd,0) then ifnull(swap_from_amount_usd,0) else ifnull(swap_to_amount_usd,0) end as usd_val, case when swap_from_mint = mint then 'S' else 'B' end as BS FROM cte_stg ), cte_counts as ( select owner,count(*) as tx_count, sum(rn_tx) as rn_tx_sum from cte group by owner ), cte_counts2 as ( select owner from cte_counts order by rn_tx_sum desc limit 5 ) , cte_bonding as ( select owner from cte order by ts asc, pre_balance desc limit 1 ) --select * from cte_bonding , cte_transit_tx as ( select tx_id from cte c where owner in (select owner from cte_bonding) and balance = 0 ) --select * from cte_transit_tx , cte_transit_acc as ( select owner from cte where tx_id in (select tx_id from cte_transit_tx) and owner <> (select owner from cte_bonding) ), cte_ray_tx as ( select tx_id from cte where owner in (select owner from cte_transit_acc) and balance = 0 ) --select * from cte_ray_tx , cte_ray_acc as ( select owner from cte where tx_id in (select tx_id from cte_ray_tx) and pre_balance = 0 and balance > 0 ), cte_amm as ( select owner from cte_bonding union all select owner from cte_ray_acc union all select owner from cte_transit_acc union all select owner from cte_counts2 ), cte_amm2 as ( select distinct owner from cte_amm ) --select * from cte_amm2 , cte_0 as ( select (case when a.owner is not null then 1 else 0 end) as amm, c.* from cte c left join cte_amm2 a on c.owner = a.owner --order by ts asc ), cte_mm as ( select row_number() over(partition by tx_id order by abs(usd_val) desc) as rn, tx_id, pre_balance as mm_pre, balance as mm_post, (balance - pre_balance) * -1 as mm_diff, (1000000000 - balance) as holder_tkn_bal from cte c where owner = '5Q544fKrFoe6tsEbD7S8EmxGTJYAKtTVhAW5Q5pge4j1' ), cte_00 as ( select row_number() over(partition by tx_id order by abs(usd_val) desc) as rn, * from cte_0 where owner <> '5Q544fKrFoe6tsEbD7S8EmxGTJYAKtTVhAW5Q5pge4j1' and usd_val is not null ) , cte2 AS ( SELECT TIMESTAMPADD(SECOND, FLOOR(DATEDIFF(SECOND, '1970-01-01 00:00:00', ts) / 15) * 15, '1970-01-01 00:00:00') AS interval_start, mint, sum(case when holder > 0 then holder else 0 end) as abs_holder, sum(sold_out) as sold_out, sum(new_buy) as new_buy, sum(new_buy-sold_out) as new_sold_activity, SUM(holder) AS holder, SUM(holder_orig) AS holder_orig, sum(case when BS = 'B' then USD_VAL else USD_VAL*-1 end) as net_balance, sum(case when owner = swapper and usd_val is not null then case when BS = 'B' then USD_VAL else USD_VAL*-1 end else 0 end) as net_balance_adj, sum(case when owner <> swapper and usd_val is not null then case when BS = 'B' then USD_VAL else USD_VAL*-1 end else 0 end) as net_balance_adj_nonswapper, sum(case when BS = 'B' then swap_to_amount else swap_from_amount*-1 end) as net_tkn_adj, sum(case when owner <> swapper then mm_diff else 0 end) as net_tkn_adj_nonswapper, --sum(case when BS = 'B' then swap_from_amount_usd else swap_to_amount_usd*-1 end) as net_balance_adj, sum(case when owner = swapper and usd_val is not null then case when BS = 'B' then 1 else -1 end else 0 end) as bs_adj, max(holder_tkn_bal) as holder_tkn_bal_f, COUNT(*) AS tx_counts FROM cte_00 c left join cte_mm m on c.tx_id = m.tx_id and m.rn = 1 where c.rn = 1 -- FROM cte_0 where amm = 0 GROUP BY interval_start, mint ORDER BY interval_start ASC ) --select * from cte2 order by interval_start asc , cte_holders AS ( SELECT *, SUM(holder) OVER(ORDER BY interval_start ASC) AS holders97, SUM(holder_orig) OVER(ORDER BY interval_start ASC) AS holder_orig_roll, SUM(net_balance) OVER(ORDER BY interval_start ASC) AS net_balance_roll, SUM(net_balance_adj) OVER(ORDER BY interval_start ASC) AS net_balance_adj_roll, SUM(net_balance_adj_nonswapper) OVER(ORDER BY interval_start ASC) AS net_balance_adj_nonswapper_roll, SUM(net_tkn_adj) OVER(ORDER BY interval_start ASC) AS net_tkn_adj_roll, SUM(net_tkn_adj_nonswapper) OVER(ORDER BY interval_start ASC) AS net_tkn_adj_nonswapper_roll, SUM(bs_adj) OVER(ORDER BY interval_start ASC) AS bs_adj_roll FROM cte2 ) select * from cte_holders where holder_tkn_bal_f > 0 order by interval_start asc

Query	Creator	Created
peak_to_cur solana swaps	kgmm2019	Mar 19, 2025
holders_main_acc_selection solana	kgmm2019	Jan 30, 2025
cross_check solana	kgmm2019	Apr 24, 2025
buysell_tx solana	kgmm2019	Jun 12, 2025
holders_main_bot_dollar_flow solana swaps	kgmm2019	Feb 8, 2025
yappiest-sapphire solana swaps	kgmm2019	Mar 25, 2025
meteora solana swaps	kgmm2019	Apr 1, 2025
top accounts SOL amt solana	kgmm2019	Aug 28, 2024

kgmm2019