flow_price as (

select

date_trunc('hour', timestamp) as hour,

avg(price_usd) as price_usd

from flow.core.fact_prices

where symbol = 'FLOW'

group by 1

),

info as (

select

block_timestamp,

right(nft_collection,len(nft_collection) - 19) as nft_collection,

contract_name as market,

case when currency='A.1654653399040a61.FlowToken' then price*price_usd else price end as price_usd

from flow.core.ez_nft_sales x

join flow.core.dim_contract_labels y on x.marketplace=y.event_contract

join flow_price z on trunc(x.block_timestamp,'hour') = z.hour

where nft_collection in ('A.329feb3ab062d289.UFC_NFT','A.e4cf4bdc1751c65d.AllDay','A.0b2a3299cc857e29.TopShot',

'A.0d9bc5af3fc0c2e3.TuneGO','A.329feb3ab062d289.RaceDay_NFT')

)

SELECT

trunc(block_timestamp,'day') as date,

nft_collection,--market,

min(price_usd) as floor_price,

avg(floor_price) OVER(partition by nft_collection ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW ) as floor_price_ma_7day

from info where date>=CURRENT_DATE-INTERVAL '3 MONTHS'

group by 1,2 order by 1 asc