WITH token_holdings AS ( SELECT block_timestamp, post_tokens.value:owner AS wallet, post_tokens.value:mint AS token, post_tokens.value:uiTokenAmount:uiAmount AS amount, ROW_NUMBER() OVER (PARTITION BY wallet, token ORDER BY block_timestamp DESC) AS rn FROM solana.core.fact_transactions, LATERAL FLATTEN(input => post_token_balances) post_tokens WHERE SUCCEEDED = 'True' AND post_tokens.value:owner = wallet AND post_tokens.value:mint = 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' AND block_timestamp >= '2022-02-01' -- and block_timestamp::date > CURRENT_DATE - 3 ORDER BY wallet, token, rn ) , dust_holder as ( select wallet, case when amount < 10 then 'a. Hold < 10 $DUST' when amount < 375 then 'b. 10 < Hold < 375 $DUST' when amount < 1000 then 'c. 375 < Hold < 1k $DUST' when amount < 10000 then 'd. 1k < Hold < 10k $DUST' -- when amount < 100000 then 'e. 10k < Hold < 100k $DUST' else 'e. Hold > 10k $DUST' end as type FROM token_holdings WHERE rn = 1 AND amount IS NOT NULL AND amount <> '' order by amount desc ) , t00b_mint as ( select * from solana.core.fact_events where program_id = 'pmvYY6Wgvpe3DEj3UX1FcRpMx43sMLYLJrFTVGcqpdn' and inner_instruction:instructions[2]:parsed:info:amount = '371250000000' and succeeded = TRUE and block_timestamp>= '2022-09-06'), base as (select inner_instruction:instructions[2]:parsed:info:authority as minter, t.block_timestamp, post_tokens.value:mint as t00bs_address, count(*) from solana.core.fact_transactions t join t00b_mint m on m.tx_id = t.tx_id, LATERAL FLATTEN(input => post_token_balances) post_tokens where t00bs_address != 'DUSTawucrTsGU8hcqRdHDCbuYhCPADMLM2VcCb8VnFnQ' and t.block_timestamp >= '2022-09-06' and t.succeeded = 'TRUE' group by 1,2,3), base2 as ( select block_timestamp as dates, t00bs_address::string as t00bs_addresses, minter::string as nft_owner from base union select block_timestamp, mint::string, purchaser::string as nft_owner from solana.core.fact_nft_sales where succeeded = 'TRUE' and mint in (select t00bs_address from base) and block_timestamp >= '2022-09-06' union select block_timestamp, mint::string, tx_to::string from solana.core.fact_transfers where mint in (select t00bs_address from base) and block_timestamp >= '2022-09-06' and tx_id in (select tx_id from solana.core.fact_events where succeeded = 'TRUE' and block_timestamp >= '2022-09-06') and index not in ('3.0', '7.2', '0.2', '5.6', '2.2', '5.4')), base3 as (select t00bs_addresses, max(dates) as latest_transference from base2 group by 1), base4 as (select latest_transference, nft_owner, b.t00bs_addresses from base2 a join base3 b on a.t00bs_addresses = b.t00bs_addresses and a.dates = b.latest_transference), base5 as (select *, 1 as numbering from base4), base6 as (select nft_owner, sum(numbering) as nft_owned, case when nft_owned = 1 then 'Own 1 t00bs 🐟' when nft_owned = 2 then 'Own 2 t00bs 🐟🐟' when nft_owned = 3 then 'Own 3 t00bs 🐟🐟🐟' when nft_owned = 4 then 'Own 4 t00bs 🦈' else 'Own >= 5 t00bs 🐳' end as owner_type, 1 as numbering from base5 group by 1 order by 2 desc) select owner_type, type, sum(numbering) as counts from base6 join dust_holder on nft_owner = wallet group by 1, 2 -- select * from base6

t00b Mint and Sales