(

select distinct nft_address,

case

when nft_address='0x4b10701bfd7bfedc47d50562b76b436fbb5bdb3b' then 'Lil Nouns'

when nft_address='0x3055e8e501571de36f11520fa3defb631fe7806a' then 'Warner Bros'

when nft_address='0x9c8ff314c9bc7f6e59a9d9225fb22946427edc03' then 'Nouns'

when nft_address='0x4393c02e65cd9596d0578813aa225a72312b423b' then 'Murakamiflowers'

else 'others'

end as NFT_NAME

,count(distinct tx_hash) as count from ethereum.core.ez_nft_transfers

where nft_to_address=lower('0x0bc3807ec262cb779b38d65b38158acc3bfede10')

group by 1 ,2

order by 3 desc

),

price as

(

select nft_address,price, row_number() over (partition by nft_address order by block_timestamp desc) as rn from ethereum.core.ez_nft_sales

where nft_address in (select distinct nft_address from dist)

and event_type='sale'

qualify rn=1

order by block_timestamp desc

)

select dist.*,coalesce(dist.count * p.price,0) as NFT_value from dist left join price p on dist.nft_address=p.nft_address