select

nft_address ,

sum(price_usd) as volume

from ethereum.core.ez_nft_sales

where price_usd is not null

and block_timestamp::date >= CURRENT_DATE - 90

group by 1

order by 2 desc

limit 10),

final as (

select

seller_address,

case when project_name = 'optimistic explorer - get started nft' then 'optimistic explorer' else project_name end as collections,

count(DISTINCT(tx_hash)) as total_sales,

count(DISTINCT(seller_address)) total_buyer,

count(DISTINCT(seller_address)) as total_seller,

sum(price_usd) as total_volume, avg(price_usd) as avg_price,

count(DISTINCT(tokenid)) as total_nfts

from ethereum.core.ez_nft_sales a

join ethereum.core.dim_labels b on a.nft_address = b.address

where nft_address in ( select nft_address from top_10)

and block_timestamp::date >= CURRENT_DATE - 90

group by 1,2)

select

collections ,

case

when total_nfts = 1 then '1 NFT'

when total_nfts = 2 then '2 NFTs'

when total_nfts = 3 then '3 NFTs'

when total_nfts = 4 then '4 NFTs'

when total_nfts >= 5 then 'more than 5 NFTs'

end as type,

count(DISTINCT(seller_address)) as buyers