from flow.core.fact_hourly_prices

where token = 'Flow'

and RECORDED_HOUR::date >= '2023-01-01'

group by 1)

,

market as ( select date(block_timestamp) as date,nft_collection, tx_id, nft_id, buyer, seller,

case when currency in ('A.1654653399040a61.FlowToken','A.ead892083b3e2c6c.FlowUtilityToken') then price*avg_price

else price end as volume

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

where block_timestamp::date >= '2023-01-01'

and marketplace in ('A.4eb8a10cb9f87357.NFTStorefront','A.4eb8a10cb9f87357.NFTStorefrontV2')

select trunc(date,'week') as weekly, count(DISTINCT(nft_collection)) as total_collection, count(DISTINCT(nft_id)) as nfts,

count(DISTINCT(tx_id)) as sales, sum(sales) over (order by weekly asc) as cum_sales,

count(DISTINCT(seller)) as sellers, count(DISTINCT(buyer)) as buyers, sum(volume) as usd_volume,

avg(volume) as avg_price, max(volume) as max_price, median(volume) as median_price,

avg(usd_volume) over (order by weekly rows between 1 preceding and 0 following) as avg_7_day_volume,

avg(usd_volume) over (order by weekly rows between 4 preceding and 0 following) as avg_30_day_volume

from market

group by 1