select

timestamp::date as day,

avg(price_usd) as flow_price

from flow.core.fact_prices

where symbol = 'FLOW'

group by 1

),

base as (

select

*,

split_part(marketplace , '.' , 3) as marketplaces,

case when currency ilike '%flow%' 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'

select

date_trunc('day', block_timestamp) as date,

case when date = '2022-11-24' then 'Thanksgiving day' when date > '2022-11-24' then 'After Thanksgiving' else 'Before Thanksgiving' end as period,

count(distinct tx_id) as sales_count,

count(distinct buyer) as unique_buyer,

count(distinct seller) as unique_seller,

avg(flow_price) as flow_price,

sum(nft_price) as sales_volume,

avg(nft_price) as avg_nft_price,

median(nft_price) as median_nft_price,

avg(sales_count) over (order by date, date rows between 6 preceding and current row) as ma7_sales_count,

avg(unique_buyer) over (order by date, date rows between 6 preceding and current row) as ma7_unique_buyer,

avg(unique_seller) over (order by date, date rows between 6 preceding and current row) as ma7_unique_seller,

avg(sales_volume) over (order by date, date rows between 6 preceding and current row) as ma7_sales_volume,

avg(avg_nft_price) over (order by date, date rows between 6 preceding and current row) as ma7_avg_nft_price,

avg(median_nft_price) over (order by date, date rows between 6 preceding and current row) as ma7_median_nft_price

from base

group by 1, 2