TOKEN,

avg(PRICE_USD) as price_usd

from flow.core.fact_prices

where token = 'Flow'

group by date,token),

t2 as ( select date(block_timestamp) as date,

tx_id,seller,buyer,currency,

case when currency ilike 'FLOW' then price*price_usd else price end as total_amount

from flow.core.ez_nft_sales x join flow.core.dim_allday_metadata y on x.nft_id = y.nft_id

left outer join t1 z on x.block_timestamp::date = z.date

where TX_SUCCEEDED = 'TRUE'

and NFLALLDAY_ID is not null),

t3 as (select trunc(date,'day') as date,

case when date < '2022-11-25' then 'Pre Thanksgiving'

when date = '2022-11-25' then 'During Thanksgiving'

when date > '2022-11-25' then 'After Thanksgiving' end as type,

count(DISTINCT tx_id) as total_transaction,

count(DISTINCT buyer) as total_buyer,

count(DISTINCT seller) as total_seller,

sum(total_amount) as total_usd_vol,

min(total_amount) as min_vol,

avg(total_amount) as avg_vol,

avg(avg_vol) over (order by date rows between 1 preceding and current row) as avg_7_day_moving,

max(total_amount) as max_vol

from t2

where date >= '2021-11-05'

GROUP by date,type)

select

type,

avg(total_transaction) as avg_transaction ,

avg(total_buyer) as avg_buyer,