with mint_date as ( select coalesce(min(block_timestamp), (select min(block_timestamp) from solana.core.fact_nft_mints)) as first_mint from solana.core.fact_nft_mints mints inner join solana.core.dim_labels labels on mints.mint = labels.address where label_type = 'nft' and succeeded = TRUE and label = '{{nft_project}}' ), nft_holders as ( select distinct mint, last_value(purchaser) over (partition by mint order by block_timestamp) as purchaser from ( select mint, purchaser, block_timestamp from solana.core.fact_nft_mints mints inner join solana.core.dim_labels labels on mints.mint = labels.address and labels.label_type = 'nft' and labels.label = '{{nft_project}}' where block_timestamp >= (select * from mint_date) and succeeded = TRUE union all select mint, purchaser, block_timestamp from solana.core.fact_nft_sales sales inner join solana.core.dim_labels labels on sales.mint = labels.address and labels.label_type = 'nft' and labels.label = '{{nft_project}}' where block_timestamp >= (select * from mint_date) and succeeded = TRUE ) ), other_nfts as ( select purchaser, sum(ifnull(sol_amount,0)) as sol from ( select mints.mint, mints.purchaser, mint_price as sol_amount, block_timestamp from solana.core.fact_nft_mints mints inner join solana.core.dim_labels labels on mints.mint = labels.address and labels.label_type = 'nft' and labels.label <> '{{nft_project}}' inner join nft_holders on nft_holders.purchaser = mints.purchaser where succeeded = TRUE union all select sales.mint, sales.purchaser, sales_amount as sol_amount, block_timestamp from solana.core.fact_nft_sales sales inner join solana.core.dim_labels labels on sales.mint = labels.address and labels.label_type = 'nft' and labels.label <> '{{nft_project}}' inner join nft_holders on nft_holders.purchaser = sales.purchaser where succeeded = TRUE ) sales group by 1 ), binned as ( select case when sol < 10 then floor(sol)::string when sol < 100 then (floor(sol/10.0)*10)::string when sol < 1000 then (floor(sol/100.0)*100)::string when sol < 10000 then (floor(sol/1000.0)*1000)::string when sol < 100000 then (floor(sol/10000.0)*10000)::string when sol < 1000000 then (floor(sol/100000.0)*100000)::string when sol < 10000000 then (floor(sol/1000000.0)*1000000)::string when sol < 100000000 then (floor(sol/10000000.0)*10000000)::string else sol::string end as bin, count(*) as holders from other_nfts group by 1 ) select * from binned

Solana NFT Projects Dashboard