(SELECT contract_name as cn, count(distinct mint) as total_nfts from solana.core.dim_nft_metadata

GROUP by 1 ),

mint_data as

(SELECT cn, mint as m,total_nfts from solana.core.dim_nft_metadata

INNER join tokens on cn = contract_name) ,

top_contracts as

(SELECT cn,total_nfts, min(sales_amount) as floor_price, floor_price*total_nfts as market_cap_in_sol, market_cap_in_sol*32 as market_cap_in_usd,

row_number() over (order by market_cap_in_usd desc ) as rn

from

(SELECT * from solana.core.fact_nft_sales

inner join mint_data

on m = mint )

where succeeded = TRUE and block_timestamp >= CURRENT_DATE - 7

GROUP by 1,2

order by 4 desc),

purchaser as

(SELECT purchaser, cn from

( SELECT purchaser, cn from solana.core.fact_nft_sales

inner join mint_data

on m = mint )

where cn in (SELECT cn from top_contracts order by market_cap_in_usd desc limit 5 )

GROUP by 1,2 ),

purchaser1 as

( SELECT purchaser as p , cn as contract_name from

( SELECT purchaser, cn from solana.core.fact_nft_sales

inner join mint_data

on m = mint )

where cn in (SELECT cn from top_contracts order by market_cap_in_usd desc limit 100) and cn not in (SELECT cn from top_contracts order by market_cap_in_usd desc limit 5 )

GROUP by 1,2 )

SELECT * from

(SELECT *, crossover/total_nfts*100 as percent_crossover ,

row_number() over (partition by cn_ order by crossover desc ) as rn_

from