tx_id,

buyer,

seller,

nft_id,

nft_collection,

case when nft_collection = 'A.0b2a3299cc857e29.TopShot' then 'NBA Top Shot'

when nft_collection = 'A.329feb3ab062d289.UFC_NFT' then 'UFC Strike'

when nft_collection = 'A.329feb3ab062d289.NFL_NFT' then 'NFL AllDay'

when nft_collection = 'A.87ca73a41bb50ad5.Golazos' then 'La Liga Golazos'

when nft_collection = 'A.4eded0de73020ca5.CricketMoments' then 'Cricket (Fancraze)'

when nft_collection = 'A.eee6bdee2b2bdfc8.Basketballs' then 'Basketballs'

when nft_collection = 'A.329feb3ab062d289.RaceDay_NFT' then 'Race Day' else nft_collection end as collection,

price,

from flow.nft.ez_nft_sales

where block_timestamp::date >= '2023-01-01'

and TX_SUCCEEDED = 'TRUE'

and nft_collection in ('A.329feb3ab062d289.UFC_NFT'))

,

price as ( select hour,

avg(price) as avg_price

from flow.price.ez_prices_hourly

where symbol = 'FLOW'

group by 1)

,

volume_usd as ( select hourly,

tx_id,

buyer,

seller,

nft_id,

nft_collection,

collection,

price,

currency,

case when currency ilike '%flow%' then price*avg_price else price end as volume_usd