SELECT timestamp::date as pricedate, avg(PRICE_USD) as pricee

from flow.core.fact_prices

where SYMBOL = 'FLOW'

GROUP BY pricedate

)

SELECT date_trunc('day', block_timestamp) as date,

case when date > '2022-11-24' THEN 'After ThanksGiving' when date = '2022-11-24' THEN 'ThanksGiving'

else 'Before ThanksGiving' end as period,

COUNT(DISTINCT TX_ID) as sale_count,

sum(case when CURRENCY = 'A.1654653399040a61.FlowToken' then PRICE * fp.pricee else PRICE end) as usd_volume,

avg(case when CURRENCY = 'A.1654653399040a61.FlowToken' then PRICE * fp.pricee else PRICE end) as avg_usd_volume,

COUNT(DISTINCT SELLER) as sellers,

COUNT(DISTINCT BUYER) as buyers,

sum(usd_volume) over (order by date) as cumu_usd_volume,

sum(sale_count) over (order by date) as cumu_sale_count

FROM flow.core.ez_nft_sales join flowprice fp on fp.pricedate = date(BLOCK_TIMESTAMP)

where TX_SUCCEEDED = 'TRUE'

and date(block_timestamp) >= CURRENT_DATE - INTERVAL '1 YEAR'

and nft_collection = 'A.e4cf4bdc1751c65d.AllDay'

GROUP by 1,2