select timestamp::date as day,

avg (price_usd) as usdprice

from flow.core.fact_prices

where symbol = 'FLOW'

and source = 'coinmarketcap'

group by 1

,

Thanksgiving_info as

(

select 'Thanksgiving Week' as timespan,

buyer,

count (distinct tx_id) as Sales_Count,

count (distinct nft_id) as NFTs_Count,

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

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

where nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

and tx_succeeded = 'TRUE'

and block_timestamp >= '2022-11-24' and block_timestamp <= '2022-11-30'

group by 1,2

,

Other_days_info as

select 'Other Days' as timespan,

buyer,

count (distinct tx_id) as Sales_Count,

count (distinct nft_id) as NFTs_Count,

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

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

where nft_collection = 'A.e4cf4bdc1751c65d.AllDay'