select

TIMESTAMP::date as p_date,

avg (price_usd) as flow_price

from flow.core.fact_prices

where symbol ='FLOW' and source ='coinmarketcap'

group by 1

)

, nft_price as (

SELECT*,

price * b.flow_price as nft_usd_price

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

)

,raceday_buyer as (

select

block_timestamp,

split(nft_collection, '.')[2] as collection,

tx_id,

buyer,

nft_id,

nft_usd_price

from nft_price

where nft_collection = 'A.329feb3ab062d289.RaceDay_NFT'

and tx_succeeded = 'TRUE'

)

, other_collec_buyer as (

select

block_timestamp,

split(nft_collection, '.')[2] as collection,

tx_id,

buyer,

nft_id,

nft_usd_price

from nft_price a JOIN raceday_buyer b USING(buyer)

where nft_collection != 'A.329feb3ab062d289.RaceDay_NFT'

and tx_succeeded = 'TRUE'