select TIMESTAMP::date as day, avg (price_usd) as flowprice

from flow.core.fact_prices

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

group by 1),

table1 as (

select block_timestamp,

buyer,

tx_id,

case when t1.currency ='A.1654653399040a61.FlowToken' then t1.price * t2.flowprice else t1.price end as Volume

from flow.core.ez_nft_sales t1 join flowpricet t2 on t1.block_Timestamp::Date = t2.day

where nft_collection = 'A.329feb3ab062d289.RaceDay_NFT'

and tx_succeeded = 'TRUE')

select count (distinct t1.nft_collection) as Collections_Count,

count (distinct t1.buyer) as Buyers_Count,

count (distinct t1.nft_id) as Tokens_Count,

sum (case when t1.currency ='A.1654653399040a61.FlowToken' then t1.price * t2.flowprice else t1.price end) as Total_Volume,

avg (case when t1.currency ='A.1654653399040a61.FlowToken' then t1.price * t2.flowprice else t1.price end) as Average_Volume

from flow.core.ez_nft_sales t1 join flowpricet t2 on t1.block_Timestamp::Date = t2.day

join table1 t3 on t1.buyer = t3.buyer and t1.block_timestamp > t3.block_timestamp

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