with balance as ( SELECT PARSE_JSON(helius_apis.balances( 'mainnet', -- the network, 'mainnet' or 'devnet' '6DNxxtkJZTNERmk7qJ9gzQwa4LTxJwadrgYwDnfcjn1G' -- the address to retrieve the balance )) as data, data:data:nativeBalance as raw_sol_holding, data:data:tokens as tokens ) , sol_price as ( select recorded_hour as latest_date, avg(close) as sol_price from solana.core.fact_token_prices_hourly where symbol = 'SOL' group by recorded_hour, symbol qualify row_number() over (partition by symbol order by recorded_hour desc) = 1 ) , curr_price as ( select symbol, avg(close) as price from solana.core.fact_token_prices_hourly group by 1, recorded_hour qualify row_number() over (partition by symbol order by recorded_hour desc) = 1 ) , price as ( select symbol, recorded_hour, avg(close) as price from solana.core.fact_token_prices_hourly group by 1,2 ) , gen3_mint_tx as ( select block_timestamp, tx_id, instruction:accounts[6] as mint, instruction:accounts[5] as minter from solana.core.fact_events where succeeded = TRUE and block_timestamp > '2023-07-05' and program_id = 'CSGrdwbJ5z58tLGKjjcmiNMj8bG1Zazthk3cXMrbSZoX' and instruction:accounts[13] = '8Rt3Ayqth4DAiPnW9MDFi63TiQJHmohfTWLMQFHi4KZH' ) , gen3_mints as ( select distinct mint from gen3_mint_tx ) , extended_label as ( select address as mint, label, label_type from solana.core.dim_labels where label_type = 'nft' UNION select mint, 'SMB Gen3' as label, 'nft' as label_type from gen3_mints ) , floor_mint as ( SELECT label as collection, min(sales_amount) as floor_price_sol, floor_price_sol as floor_usd FROM solana.core.fact_nft_sales s join extended_label using(mint) join sol_price where succeeded = TRUE and block_timestamp::date >= CURRENT_DATE - 1 and sales_amount > 0 group by 1, sol_price ) , base_balance as ( select 'So11111111111111111111111111111111111111112'::varchar as raw_mint, 9 as dec, raw_sol_holding as raw_amount from balance UNION select token.value:mint as raw_mint, token.value:decimals as dec, token.value:amount as raw_amount from balance, LATERAL FLATTEN(input => tokens) token where raw_mint not in ( 'o1Mw5Y3n68o8TakZFuGKLZMGjm72qv4JeoZvGiCLEvK', -- SCAM COPE TOKEN 'FfpyoV365c7iR8QQg5NHGCXQfahbqzY67B3wpzXkiLXr' -- BOO ) ) , balance_all as ( select case when label_type = 'nft' then c.label else symbol end as label, case when label_type is null then 'Token' else 'NFT' end as label_type, raw_mint, raw_amount, raw_amount/POW(10, dec) as amount from base_balance a -- join solana.core.dim_labels b on raw_mint = address left join extended_label c on raw_mint = mint left join solana.core.dim_tokens d on raw_mint = token_address where raw_amount > 0 ) , base as ( select a.*, price, amount as volume_usd, floor_usd from balance_all a left join curr_price b on upper(label) = symbol left join floor_mint c on label = collection ) , base2 as ( select upper(label) as "TOKEN NAME", initcap(label_type) as type, amount, round(zeroifnull(volume_usd) + zeroifnull(floor_usd), 2) as est_usd, raw_mint as "MINT ADDRESS" from base having est_usd > 0 order by est_usd desc ) , fact_price as ( select symbol, avg(close) as sol_price from solana.core.fact_token_prices_hourly where symbol = 'SOL' group by 1, recorded_hour qualify row_number() over (partition by symbol order by recorded_hour desc) = 1 ) , fact_stake as ( select block_timestamp as f_block_timestamp, tx_id as f_tx_id, index as f_index, event_type as f_event_type, instruction from solana.core.fact_staking_lp_actions ) , base_stake as ( select *, signers [0] as fee_payer, post_tx_staked_balance / pow(10, 9) as net_stake, row_number() over ( partition by stake_account order by block_id desc, index desc, tx_id ) as rn, fact_stake.instruction :parsed :info as info from solana.core.ez_staking_lp_actions left join fact_stake on ( tx_id = fact_stake.f_tx_id and block_timestamp = fact_stake.f_block_timestamp and index = fact_stake.f_index and event_type = fact_stake.f_event_type ) where SUCCEEDED = TRUE and block_timestamp is not null and event_type not in ('setLockup') and not ( event_type = 'authorize' and stake_authority is null ) and not ( stake_authority is null and event_type = 'authorizeWithSeed' and fact_stake.instruction :parsed :info :authorityType = 'Withdrawer' ) ) , base_with_staker as ( select *, case when stake_authority is null and event_type = 'withdraw' then lag(stake_authority) ignore nulls over ( partition by stake_account order by rn desc ) -- when stake_authority is null -- and event_type = 'withdraw' then info :withdrawAuthority when stake_authority is null and event_type = 'authorizeChecked' then info :newAuthority when stake_authority is null and event_type = 'initializeChecked' then info :staker else stake_authority end as staker from base_stake ) , latest_stake as ( select staker, sum(net_stake) as total_stake from base_with_staker where rn = 1 group by staker ) , labeled as ( select staker, total_stake from latest_stake where staker = '6DNxxtkJZTNERmk7qJ9gzQwa4LTxJwadrgYwDnfcjn1G' order by total_stake desc ) , all_stake as( select *, total_stake * sol_price as stake_usd from labeled join fact_price ) , aggregated_tokens_bins as ( SELECT "TOKEN NAME", type, SUM(amount) as total_amount, round(AVG(zeroifnull(est_usd)), 2) as summed_usd, NULL as "MINT ADDRESS" FROM ( SELECT * FROM base2 UNION ALL SELECT 'Validator Stake' as "TOKEN NAME", 'SOL Stake' as type, total_stake, round(zeroifnull(stake_usd), 2) as est_usd, null as "MINT ADDRESS" FROM all_stake HAVING est_usd > 0 ) subquery GROUP BY "TOKEN NAME", type ) , aggregated_tokens_with_bins as ( SELECT "TOKEN NAME", type, total_amount, summed_usd, CASE WHEN summed_usd BETWEEN 0 AND 0.2 THEN '0-0.2' WHEN summed_usd > 0.2 AND summed_usd <= 0.4 THEN '0.2-0.4' WHEN summed_usd > 0.4 AND summed_usd <= 0.6 THEN '0.4-0.6' WHEN summed_usd > 0.6 AND summed_usd <= 0.8 THEN '0.6-0.8' WHEN summed_usd > 0.8 AND summed_usd <= 1 THEN '0.8-1' WHEN summed_usd > 1 AND summed_usd <= 2 THEN '1-2' WHEN summed_usd > 2 AND summed_usd <= 3 THEN '2-3' WHEN summed_usd > 3 AND summed_usd <= 4 THEN '3-4' WHEN summed_usd > 4 AND summed_usd <= 5 THEN '4-5' WHEN summed_usd > 5 AND summed_usd <= 6 THEN '5-6' WHEN summed_usd > 6 AND summed_usd <= 7 THEN '6-7' WHEN summed_usd > 7 AND summed_usd <= 8 THEN '7-8' WHEN summed_usd > 8 AND summed_usd <= 9 THEN '8-9' WHEN summed_usd > 9 AND summed_usd <= 10 THEN '9-10' WHEN summed_usd > 10 AND summed_usd <= 15 THEN '10-15' WHEN summed_usd > 15 AND summed_usd <= 20 THEN '15-20' WHEN summed_usd > 20 AND summed_usd <= 30 THEN '20-30' WHEN summed_usd > 30 AND summed_usd <= 40 THEN '30-40' WHEN summed_usd > 40 AND summed_usd <= 50 THEN '40-50' WHEN summed_usd > 50 AND summed_usd <= 60 THEN '50-60' WHEN summed_usd > 60 AND summed_usd <= 70 THEN '60-70' WHEN summed_usd > 70 AND summed_usd <= 80 THEN '70-80' ELSE '80+' END AS est_usd_bin, "MINT ADDRESS" FROM aggregated_tokens_bins ) , main_query_bins AS ( SELECT est_usd_bin as bin, SUM(total_amount) as y_value FROM aggregated_tokens_with_bins WHERE (type != 'Token') GROUP BY est_usd_bin ), sec_balance AS ( SELECT PARSE_JSON(helius_apis.balances( 'mainnet', '6DNxxtkJZTNERmk7qJ9gzQwa4LTxJwadrgYwDnfcjn1G' )) AS data, data:data:nativeBalance AS raw_sol_holding, data:data:tokens AS tokens ), sec_base_balance AS ( SELECT 'So11111111111111111111111111111111111111112'::VARCHAR AS raw_mint, 9 AS dec, raw_sol_holding AS raw_amount FROM sec_balance UNION SELECT token.value:mint AS raw_mint, token.value:decimals AS dec, token.value:amount AS raw_amount FROM sec_balance, LATERAL FLATTEN(input => tokens) token WHERE raw_mint NOT IN ( 'o1Mw5Y3n68o8TakZFuGKLZMGjm72qv4JeoZvGiCLEvK', 'FfpyoV365c7iR8QQg5NHGCXQfahbqzY67B3wpzXkiLXr' ) ), sec_balance_all AS ( SELECT COALESCE(c.label, a.raw_mint) AS label, COALESCE(c.label_type, 'Unknown') AS label_type, raw_mint, raw_amount, raw_amount/POW(10, dec) AS amount FROM sec_base_balance a LEFT JOIN solana.core.dim_labels c ON a.raw_mint = c.address WHERE raw_amount > 0 AND (COALESCE(c.label_type, 'Unknown') = 'Unknown' OR c.label_type = 'nft') ) , sec_sol_price as ( select avg(close) as sol_price from solana.core.fact_token_prices_hourly where symbol = 'SOL' group by recorded_hour, symbol qualify row_number() over (partition by symbol order by recorded_hour desc) = 1 ) , sec_gen3_mint_tx as ( select block_timestamp, tx_id, instruction:accounts[6] as mint, instruction:accounts[5] as minter from solana.core.fact_events where succeeded = TRUE and block_timestamp > '2023-07-05' and program_id = 'CSGrdwbJ5z58tLGKjjcmiNMj8bG1Zazthk3cXMrbSZoX' and instruction:accounts[13] = '8Rt3Ayqth4DAiPnW9MDFi63TiQJHmohfTWLMQFHi4KZH' ) , sec_gen3_mints as ( select distinct mint from sec_gen3_mint_tx ) , sec_extended_label as ( select address as mint, label, label_type from solana.core.dim_labels where label_type = 'nft' UNION select mint, 'SMB Gen3' as label, 'nft' as label_type from sec_gen3_mints ) , sec_floor_mint as ( SELECT label as collection, min(sales_amount) as floor_price, floor_price as floor_usd FROM solana.nft.fact_nft_sales s join extended_label using(mint) join sol_price where succeeded = TRUE and block_timestamp::date >= CURRENT_DATE - 1 and sales_amount > 0 group by 1, sol_price ), sec_floor_mint_unknown AS ( SELECT mint AS collection, MIN(sales_amount) AS floor_price, floor_price AS floor_usd FROM solana.core.fact_nft_sales s WHERE mint IN ( SELECT raw_mint FROM sec_balance_all WHERE label_type = 'Unknown' OR label_type = 'Nft' ) AND succeeded = TRUE AND block_timestamp::DATE >= CURRENT_DATE - 1 AND sales_amount > 0 GROUP BY 1 ), sec_curr_price as ( select symbol, avg(close) as price from solana.core.fact_token_prices_hourly group by 1, recorded_hour qualify row_number() over (partition by symbol order by recorded_hour desc) = 1 ) , sec_base AS ( SELECT a.*, price, amount as volume_usd, COALESCE(c.floor_usd, sec_floor_mint_unknown.floor_usd) as floor_usd -- Integrated floor_mint_unknown without D alias FROM sec_balance_all a LEFT JOIN sec_curr_price b ON upper(label) = symbol LEFT JOIN sec_floor_mint c ON label = collection LEFT JOIN sec_floor_mint_unknown ON a.raw_mint = sec_floor_mint_unknown.collection -- Joining without D alias ), sec_histogram_bins AS ( SELECT CASE WHEN floor_price BETWEEN 0 AND 0.2 THEN '0-0.2' WHEN floor_price > 0.2 AND floor_price <= 0.4 THEN '0.2-0.4' WHEN floor_price > 0.4 AND floor_price <= 0.6 THEN '0.4-0.6' WHEN floor_price > 0.6 AND floor_price <= 0.8 THEN '0.6-0.8' WHEN floor_price > 0.8 AND floor_price <= 1 THEN '0.8-1' WHEN floor_price > 1 AND floor_price <= 2 THEN '1-2' WHEN floor_price > 2 AND floor_price <= 3 THEN '2-3' WHEN floor_price > 3 AND floor_price <= 4 THEN '3-4' WHEN floor_price > 4 AND floor_price <= 5 THEN '4-5' WHEN floor_price > 5 AND floor_price <= 6 THEN '5-6' WHEN floor_price > 6 AND floor_price <= 7 THEN '6-7' WHEN floor_price > 7 AND floor_price <= 8 THEN '7-8' WHEN floor_price > 8 AND floor_price <= 9 THEN '8-9' WHEN floor_price > 9 AND floor_price <= 10 THEN '9-10' WHEN floor_price > 10 AND floor_price <= 15 THEN '10-15' WHEN floor_price > 15 AND floor_price <= 20 THEN '15-20' WHEN floor_price > 20 AND floor_price <= 30 THEN '20-30' WHEN floor_price > 30 AND floor_price <= 40 THEN '30-40' WHEN floor_price > 40 AND floor_price <= 50 THEN '40-50' WHEN floor_price > 50 AND floor_price <= 60 THEN '50-60' WHEN floor_price > 60 AND floor_price <= 70 THEN '60-70' WHEN floor_price > 70 AND floor_price <= 80 THEN '70-80' ELSE '80+' END AS floor_interval, collection AS "MINT ADDRESS" FROM sec_floor_mint_unknown ) , second_query_bins AS ( SELECT floor_interval, COUNT("MINT ADDRESS") AS mint_count FROM sec_histogram_bins GROUP BY floor_interval ) , all_bins AS ( SELECT bin FROM (VALUES ('0-0.2'), ('0.2-0.4'), ('0.4-0.6'), ('0.6-0.8'), ('0.8-1'), ('1-2'), ('2-3'), ('3-4'), ('4-5'), ('5-6'), ('6-7'), ('7-8'), ('8-9'),('9-10'), ('10-15'), ('15-20'), ('20-30'), ('30-40'), ('40-50'), ('50-60'), ('60-70'), ('60-70'), ('80+')) AS t(bin) ) SELECT b.bin AS "Floor Range (SOL)", COALESCE(main_query_bins.y_value, 0) + COALESCE(second_query_bins.mint_count, 0) as "NFT Count" FROM all_bins b LEFT JOIN main_query_bins ON b.bin = main_query_bins.bin LEFT JOIN second_query_bins ON b.bin = second_query_bins.floor_interval ORDER BY CASE b.bin WHEN '0-0.2' THEN 1 WHEN '0.2-0.4' THEN 2 WHEN '0.4-0.6' THEN 3 WHEN '0.6-0.8' THEN 4 WHEN '0.8-1' THEN 5 WHEN '1-2' THEN 6 WHEN '2-3' THEN 7 WHEN '3-4' THEN 8 WHEN '4-5' THEN 9 WHEN '5-6' THEN 10 WHEN '6-7' THEN 11 WHEN '7-8' THEN 12 WHEN '8-9' THEN 13 WHEN '9-10' THEN 14 WHEN '10-15' THEN 15 WHEN '15-20' THEN 16 WHEN '20-30' THEN 17 WHEN '30-40' THEN 18 WHEN '40-50' THEN 19 WHEN '50-60' THEN 20 WHEN '60-70' THEN 21 WHEN '70-80' THEN 22 ELSE 23 END;

NFT's Floor Distribution.