select

timestamp::date as date_,

avg(price_usd) as usdprice

from flow.core.fact_prices

where symbol = 'FLOW'

and source = 'coinmarketcap'

group by 1

),

tbl as (select

CASE

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

ELSE 'Before ThanksGiving Week' end as type,

tx_id,

buyer,

seller,

nft_id,

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 median_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 A join price B on A.block_timestamp::Date = B.date_

where nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

and tx_succeeded = 'TRUE'

and block_timestamp >= '2022-01-01'

GROUP by 1,2,3,4,5),

thanksGiving as(SELECT

case

when avg_volume < 5 then 'Balance Less Than 5 USD'

when avg_volume >= 5 and avg_volume < 10 then 'average_volume 5 - 10 USD'

when avg_volume >= 10 and avg_volume < 20 then 'average_volume 10 - 20 USD'

when avg_volume >= 20 and avg_volume < 50 then 'average_volume 20 - 50 USD'