select

timestamp::date as p_date,

avg(price_usd) as usdprice

from flow.core.fact_prices

where symbol = 'FLOW'

and source = 'coinmarketcap'

group by 1

)

select

CASE

WHEN block_timestamp >= '2022-11-24' THEN 'ThanksGiving Week(2022)'

ELSE 'Pre ThanksGiving Week(2022)' end as period,

count(distinct tx_id) as Sales_Cnt,

count(distinct buyer) as Buyers_Cnt,

count(distinct seller) as Sellers_Cnt,

count(distinct nft_id) as NFTs_Cnt,

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

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

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

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

max(case when currency = 'A.1654653399040a61.FlowToken' then price*usdprice else price end) as "Max Volume($)"

from flow.core.ez_nft_sales s join pricTb p on s.block_timestamp::Date = p.p_date

where nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

and tx_succeeded = 'TRUE'

and block_timestamp >= '2022-01-01'

GROUP by 1