select hour::date as day,

avg(price) as flowprice

from flow.price.ez_prices_hourly

where symbol ='FLOW'

group by 1)

select substr(nft_collection,20) as NFT_Project,

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,

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}}'

group by 1

order by 2 desc