select

TIMESTAMP::date as p_date,

avg (price_usd) as "price($)"

from flow.core.fact_prices

where symbol ='FLOW'

and source ='coinmarketcap'

group by 1

)

select

split(nft_collection, '.')[2] as "NFT Collection",

row_number() over(order by "NFT Collection" desc) as rank,

rank || '. ' || "NFT Collection" as "Collection Rank",

min(case when a.currency ='A.1654653399040a61.FlowToken' then a.price * b."price($)" else a.price end) as "Floor Price($)"

from flow.core.ez_nft_sales a join price b on a.block_timestamp::date = b.p_date

where tx_succeeded = 'TRUE'

and block_timestamp::date = (select max(block_Timestamp::date) from flow.core.ez_nft_sales)

group by 1

order by 2