select timestamp::date as day,

avg (price_usd) as usdprice

from flow.core.fact_prices

where symbol = 'FLOW'

and source = 'coinmarketcap'

group by 1)

select 'ThanksGiving 2022 Week' as timespan,

date_trunc (day,block_timestamp) as date,

count (distinct tx_id) as Sales_Count,

count (distinct buyer) as Buyers_Count,

count (distinct seller) as Sellers_Count,

count (distinct nft_id) as NFTs_Count,

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

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

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

min (case when currency = 'A.1654653399040a61.FlowToken' then price*usdprice else price end) as Minimum_USD_Volume,

max (case when currency = 'A.1654653399040a61.FlowToken' then price*usdprice else price end) as Maximum_USD_Volume,

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

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

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

avg (Total_USD_Volume) over (order by date rows between 7 PRECEDING and current row) as Total_Sales_Moving_Average_7

from flow.core.ez_nft_sales t1 join pricet t2 on t1.block_timestamp::Date = t2.day

where nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

and tx_succeeded = 'TRUE'

and block_timestamp >= '2022-11-24'

group by 1,2

union ALL

select 'Before ThanksGiving' as timespan,

date_trunc (day,block_timestamp) as date,

count (distinct tx_id) as Sales_Count,

count (distinct buyer) as Buyers_Count,

count (distinct seller) as Sellers_Count,