-- forked from base histogram @ https://flipsidecrypto.xyz/edit/queries/6355b419-6073-4db1-bc7c-edd791529b00 with tokens as ( select * from ( values ('5MAYDfq5yxtudAhtfyuMBuHZjgAbaS9tbEyEQYAhDS5y', 'ACS') ) as t(address, symbol) ) , curr_price as ( select symbol, address, avg(close) as price from solana.price.fact_token_prices_hourly join tokens using(symbol) group by 1,2, recorded_hour qualify row_number() over (partition by symbol order by recorded_hour desc) = 1 ) , token_holdings as ( select block_timestamp, post_tokens.value:owner as wallet, post_tokens.value:mint as token, symbol, post_tokens.value:uiTokenAmount:uiAmount as amount, amount*price as amount_usd from solana.core.fact_transactions, LATERAL FLATTEN(input => post_token_balances) post_tokens -- join tokens on (post_tokens.value:mint = address) join curr_price on (post_tokens.value:mint = address) where SUCCEEDED = 'True' and symbol = 'ACS' AND block_timestamp >= '2024-02-07' -- and block_timestamp::date > CURRENT_DATE - 3 and amount is not null and amount <> '' and amount_usd > 1 qualify row_number() over (partition by wallet, token order by block_timestamp desc) = 1 ) , base as ( select wallet, symbol, amount, amount_usd from token_holdings order by amount desc ) -- select * from base select symbol, case when amount_usd < 10 then 'Hold < $10' when amount_usd < 100 then 'Hold < $100' when amount_usd < 1000 then 'Hold < $1,000' when amount_usd < 10000 then 'Hold < $10k' when amount_usd < 100000 then 'Hold < $100k' else 'Hold > $100k' end as type, -- https://count.co/sql-resources/snowflake/histograms count(wallet) as user_count, floor(amount_usd / 20) * 20 as hold_amount, floor(amount_usd / 20) * 20 + 20 as bin_end, concat( '$'||cast(floor(amount_usd / 20) * 20 as STRING), ' - ', '$'||cast(floor(amount_usd / 20) * 20 + 20 as STRING) ) as bin_range, sum(user_count) over (order by hold_amount) as cumu_address from base group by hold_amount, bin_end, bin_range, type, symbol -- having hold_amount < 10000 order by hold_amount

Solana || Access Protocol KPI