select hour::date as day,

avg(price) as flowprice

from flow.price.ez_prices_hourly

where symbol ='FLOW'

group by 1)

select date_trunc ('{{Time_Interval}}',block_timestamp) as date,

count (Distinct tx_id) as Sales,

count (distinct NFT_ID) as Traded_NFTs,

count (distinct buyer) as Buyers,

count (Distinct seller) as Sellers,

sum (case when currency in ('A.ead892083b3e2c6c.FlowUtilityToken','A.1654653399040a61.FlowToken') then price*flowprice else price end) as Sales_Volume,

avg (case when currency in ('A.ead892083b3e2c6c.FlowUtilityToken','A.1654653399040a61.FlowToken') then price*flowprice else price end) as Average_Sales_Volume,

min (case when currency in ('A.ead892083b3e2c6c.FlowUtilityToken','A.1654653399040a61.FlowToken') then price*flowprice else price end) as Floor_Price,

max (case when currency in ('A.ead892083b3e2c6c.FlowUtilityToken','A.1654653399040a61.FlowToken') then price*flowprice else price end) as Price_Ceiling,

round (sales / buyers) as Trades_Per_Trader,

sales_volume / buyers as Trading_Volume_Per_Trader,

sum (sales) over (order by date) as Cumulative_Sales,

sum (sales_volume) over (order by date) as Cumulative_Sales_Volume

from flow.nft.ez_nft_sales t1 join flowpricet t2 on t1.block_timestamp::Date = t2.day

where tx_succeeded ilike 'TRUE'

and block_timestamp >= '{{Start_Date}}' and block_timestamp <= '{{End_Date}}'

and substr(nft_collection,20) ilike '%{{NFT_Collection}}%'

group by 1

order by 1 desc