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

from flow.core.fact_prices

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

group by 1),

maintable as (

select block_timestamp::Date as date,

count (distinct tx_id) as Sales_Count,

count (distinct buyer) as Buyers_Count,

count (distinct seller) as Sellers_Count,

count (distinct nft_collection) as Collections_Count,

count (distinct marketplace) as Marketplaces_Count,

count (distinct nft_id) as Tokens_Count,

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

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

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

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

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

avg (Average_USD_Volume) over (order by date rows between 7 PRECEDING and current row) as Moving_Average_7,

avg (Average_USD_Volume) over (order by date rows between 30 PRECEDING and current row) as Moving_Average_30,

sum (total_usd_volume) over (order by date) as Cumulative_Volume,

sum (sales_count) over (order by date) as Cumulative_Sales_Count

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'

group by 1)

select avg (sales_count), avg (buyers_count), avg (sellers_count), avg (total_usd_volume)

from maintable