select

timestamp::date as day,

avg(price_usd) as flow_price

from flow.core.fact_prices

where token_contract = 'A.1654653399040a61.FlowToken'

group by 1

),

base as (

select

*,

case

when currency in ('A.ead892083b3e2c6c.FlowUtilityToken', 'A.1654653399040a61.FlowToken') then price * flow_price else price end as nft_price

from flow.core.ez_nft_sales join price on block_timestamp::date = day

where

nft_collection = 'A.e4cf4bdc1751c65d.AllDay' and tx_succeeded = 'TRUE'

),

base_2 as(

select

date_trunc('day', block_timestamp) as date,

case when date = '2022-11-24' then 'Thanksgiving day-2022'

when date > '2022-11-24' and date <='2022-12-01' then 'One week After Thanksgiving-2022'

when date < '2022-11-24' and date >='2022-11-17' then 'One week befor Thanksgiving-2022'

when date < '2022-11-17' and date >='2022-11-10' then 'Two week befor Thanksgiving-2022'

when date < '2022-11-10' and date >='2022-11-01' then 'Three week befor Thanksgiving-2022'

end as period,

tx_id,

buyer,

seller,

flow_price,

nft_price

from base

where date >='2022-11-03' and date <='2022-12-01'

select period ,count(distinct tx_id) as sales_count,

count(distinct buyer) as unique_buyer,