select

date_trunc ('{{Frequency}}',block_hour) as p_date,

avg(price_usd) as algo_Price

from algorand.core.ez_price_pool_balances

group by 1)

select

date_trunc ('{{Frequency}}',block_timestamp) as date,

NFT_MARKETPLACE as marketplace,

count(distinct tx_group_id) as "Sales Cnt",

count(distinct purchaser) as "Buyers Cnt",

count(distinct collection_name) as "Collections Cnt",

count(distinct nft_asset_id) as "NFTs Cnt",

sum(total_sales_amount) as "Sales Volume(Algo)",

sum(total_sales_amount*algo_Price) as "Sales Volume($)",

avg(total_sales_amount) as "Avg Sales Volume(Algo)",

avg(total_sales_amount*algo_Price) as "Avg Sales Volume($)",

min (total_sales_amount*algo_Price) as "Floor Price($)",

sum("Sales Cnt") over (partition by marketplace order by date) as "Cum Sales Cnt",

sum("Sales Volume(Algo)") over (partition by marketplace order by date) as "Cum Sales Volume(Algo)",

sum("Sales Volume($)") over (partition by marketplace order by date) as "Cum Sales Volume($)",

avg("Sales Volume(Algo)") over (partition by marketplace order by date rows between 7 PRECEDING and current row) as "MA 7-day Price(ALGO)",

avg("Sales Volume($)") over (partition by marketplace order by date rows between 7 PRECEDING and current row) as "MA 7-day Price($)"

from algorand.nft.ez_nft_sales a join priceTb b on a.block_timestamp::date = b.p_date

where nft_marketplace ilike any ('{{Marketplace1}}', '{{Marketplace2}}')

AND block_timestamp >= CURRENT_DATE - INTERVAL '{{Past_Weeks}} week'

and total_sales_amount > 0

group by 1,2

order by 1