select block_hour::date as p_date,

avg (price_usd) as algo_Price

from algorand.core.ez_price_pool_balances

group by 1

)

select

NFT_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($)",

median(total_sales_amount) as "Med Sales Volume(Algo)",

median(total_sales_amount*algo_Price) as "Med Sales Volume($)",

min(total_sales_amount) as "Min Sales Volume(Algo)",

min(total_sales_amount*algo_Price) as "Min Sales Volume($)",

max(total_sales_amount) as"Max Sales Volume(Algo)",

max(total_sales_amount*algo_Price) as "Max Sales Volume($)"

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

where total_sales_amount > 0

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

GROUP by 1

ORDER by 2 DESC